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

## Re: Find Cycle Based on Date

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

24 REPLIES 24
Super User

## Re: Find Cycle Based on Date

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

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

## Re: Find Cycle Based on Date

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

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

## Re: Find Cycle Based on Date

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.
Level: Powered On

## Re: Find Cycle Based on Date

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

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

Announcements

#### Power Automate Community User Group Member Badge

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

#### Power Platform World Tour

Find out where you can attend!

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

#### Microsoft Learn

Learn how to build the business apps that you need.

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,374)