cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TerryR
Helper I
Helper I

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

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

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
RandyHayes
Super User III
Super User III

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

The cycles do not overlap, so the end date would essentially be defined by the new start 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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

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.  

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,268)