cancel
Showing results for
Did you mean:
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
Super User

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Helper I

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)

24 REPLIES 24
Super User

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Helper I

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

Helper I

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

Super User

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Helper I

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

Helper I

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

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Helper I

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

Helper I

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

Announcements

#### Copilot Cookbook Challenge | WINNERS ANNOUNCED | Win Tickets to the Power Platform Conference

We are excited to announce the "The Copilot Cookbook Community Challenge random winners have been selected for the Challenge.  Thank you to everyone who participated in this challenge.    Copilot Cookbook Gallery:Power Apps Cookbook Gallery: 1. @swaminawale  1. @renatoromao     2.  @SpongYe  2.   @nickpotts10  *Please note if for any reason a winner declines, we will have another random drawing.   Check out all of the Cookbook Submissions: 1. Copilot Studio Cookbook Gallery: https://aka.ms/CS_Copilot_Cookbook_Challenge 2. Power Apps Copilot Cookbook Gallery: https://aka.ms/PA_Copilot_Cookbook_Challenge   There will be 5 chances to qualify for the final drawing: Early Bird Entries: March 1 - June 2Week 1: June 3 - June 9Week 2: June 10 - June 16Week 3: June 17 - June 23Week 4: June 24 - June 30WINNERS ANNOUNCED - JULY 8th     At the end of each week, we will draw 5 random names from every user who has posted a qualifying Copilot Studio template, sample or demo in the Copilot Studio Cookbook or a qualifying Power Apps Copilot sample or demo in the Power Apps Copilot Cookbook. Users who are not drawn in a given week will be added to the pool for the next week. Users can qualify more than once, but no more than once per week. Four winners will be drawn at random from the total qualifying entrants. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once. If they are drawn multiple times, another user will be drawn at random. Prizes:  One Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 (\$1800 value, does not include travel, lodging, or any other expenses) Winners are also eligible to do a 10-minute presentation of their demo or solution in a community solutions showcase at the event. To qualify for the drawing, templates, samples or demos must be related to Copilot Studio or a Copilot feature of Power Apps, Power Automate, or Power Pages, and must demonstrate or solve a complete unique and useful business or technical problem. Power Automate and Power Pagers posts should be added to the Power Apps Cookbook. Final determination of qualifying entries is at the sole discretion of Microsoft. Weekly updates and the Final random winners will be posted in the News & Announcements section in the communities on July 29th, 2024. Did you submit entries early?  Early Bird Entries March 1 - June 2:  If you posted something in the "early bird" time frame complete this form: https://aka.ms/Copilot_Challenge_EarlyBirds if you would like to be entered in the challenge. Early Bird Submissions: @renato Week 1 Results:  Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1.  @Mathieu_Paris 1.   @SpongYe 2.  n/a2.   @Deenuji 3.  n/a3.   @Nived_Nambiar  4.  n/a4.   @ManishSolanki 5.  n/a5.    n/a   Week 2 Results:  Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. Kasun_Pathirana1. ManishSolanki2. cloudatica2. madlad3. n/a3. SpongYe4. n/a4. n/a5. n/a5. n/a     Week 3 Results:  Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. Parul_Yadav_Neo1. n/a2. SpongYe2. n/a3. n/a3. n/a4. n/a4. n/a5. n/a5. n/a   Week 4 Results:  Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. @nickpotts10  1. @ShrushtiShah  2. @Suniti_0020 2. @swaminawale 3. n/a3. @farukhis786 4. n/a4. @ManishSolanki  5. n/a5.  n/a