cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TerryR
Level: Powered On

Find Cycle Based on Date

I want to use powerapps to identify the cycle in which a product was produced.  I have a separate table set up like this...

 

Cycle      CycleStartDate

162         2019-05-29

161         2019-04-17

160         2019-02-27

159         2018-12-11

 

For example, if the product was produced on 3/16/2019, the app needs to show that the product was produced in cycle number 160.  Any ideas on how to do that?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Find Cycle Based on Date

@TerryR 

If you have the ability to incorporate an end date into your data record, that will make the finding of the cycle immensely less complex. 

 

With that, you can find the Cycle with a formula such as this:

   Lookup(cycleDataSource, ProductDate>=CycleStartDate && ProductDate < CycleEndDate).Cycle

 

If ProductDate in the above formula was "3/16/2019", then your result would be 160

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

TerryR
Level: Powered On

Re: Find Cycle Based on Date

We finally got this working.  We had to change the date fields in the SQL lookup table to Char(10), then the formula below worked!

 

LookUp(CycleDataSource,(DateValue(CycleStartDate) <= DateValue2.SelectedDate) && DateValue(CycleEndDate) > DateValue2.SelectedDate, Cycle)

 

View solution in original post

24 REPLIES 24
Super User
Super User

Re: Find Cycle Based on Date

@TerryR 

That can get a little tricky based on what you wrote.  My first question would be, you have a start date in the cycle list, do you have an End Date?  If there is no end date, then my next question would be, does each cycle have a set number of days (thus you could calculate the end date)?

 

If either of those two are answered with a yes, then it is not too difficult.  If not, then there is some work to do.

 

Post back and we can run with it from there.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
TerryR
Level: Powered On

Re: Find Cycle Based on Date

The cycles do not overlap, so the end date would essentially be defined by the new start date.  

Highlighted
TerryR
Level: Powered On

Re: Find Cycle Based on Date

The cycles do not overlap, so the end date would essentially be defined by the new start date.  

Super User
Super User

Re: Find Cycle Based on Date

@TerryR 

Understood, but my real question is, is there any way to determine the cycle end date without looking at another record?  ex. a cycle is always 30 days, therefore if we know the start, we can determine the end.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
TerryR
Level: Powered On

Re: Find Cycle Based on Date

No there isn't.  The cycles run approximately 6-7 weeks, but the end date varies.

TerryR
Level: Powered On

Re: Find Cycle Based on Date

I should be able to add an end date to the previous record when the new cycle starts, if that helps.  It would probably be a manual change, but the start date is added manually anyhow.  

Super User
Super User

Re: Find Cycle Based on Date

@TerryR 

If you have the ability to incorporate an end date into your data record, that will make the finding of the cycle immensely less complex. 

 

With that, you can find the Cycle with a formula such as this:

   Lookup(cycleDataSource, ProductDate>=CycleStartDate && ProductDate < CycleEndDate).Cycle

 

If ProductDate in the above formula was "3/16/2019", then your result would be 160

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

TerryR
Level: Powered On

Re: Find Cycle Based on Date

You are right, that'll be simple!  Thanks for your help!

TerryR
Level: Powered On

Re: Find Cycle Based on Date

I've tried to get this to work in multiple ways, but just can't quite seem to get it.  It keeps giving mean error message.  

Since I'm entering the data, I use date in DateValue2 edit box as the date that I'm comparing with the table.

 

If I use 

    Lookup(cycleDataSource, CycleEndDate<=DateValue2 && CycleStartDate, Cycle)

I get 147, but with 

    Lookup(cycleDataSource, CycleEndDate>DateValue2 && CycleStartDate<=DateValue2, Cycle)

it is blank, with an error. The error says, "Not a valid connector ERROR response."

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 325 members 6,526 guests
Please welcome our newest community members: