Find Cycle Based on Date

[TerryR]

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?

[RandyHayes]

@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.

[TerryR]

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

[TerryR]

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

[RandyHayes]

@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.

[TerryR]

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

[TerryR]

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.  

[RandyHayes · Accepted solution]

@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

[TerryR]

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

[TerryR]

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."