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

Sequential Calculation Between Date

Hello PBI Community, 

 

I'm stuck on trying to figure out how to make a dax calculation for the following data. I need a calculation that will count sequentially by ID the days between two dates using a certain logic. Ideally need this calculation to be sliced by year, month, day, e

 

Here is the data and my current excel formula. The formula looks at if the seq is 10 than the calculation is between the complete date and the start date if the seq is greater than 10 than the calculation looks at the date difference between the complete date and the complete date previous to that. 

For example: The first ID show would be 1 in the first cell and 12 in the second. This is per ID by sequence number. Any help is greatly appreciated. Thank you!

 

ID

Item ID

StartDate

SEQ

Quantity

Complete Date

5275554

S25PG100DZXX

5/6/19

10

15

5/7/19

5275554

S25PG100DZXX

5/6/19

20

15

5/19/19

5275554

S25PG100DZXX

5/6/19

30

15

8/29/20

5275555

S25PG100DZXX

5/6/19

10

16

5/7/19

5275555

S25PG100DZXX

5/6/19

20

16

5/20/19

5275555

S25PG100DZXX

5/6/19

30

16

8/29/20

5275556

S25PG100DZXX

5/6/19

10

16

5/7/19

5275556

S25PG100DZXX

5/6/19

20

16

5/20/19

5275556

S25PG100DZXX

5/6/19

30

16

8/29/20

5278216

S25PG100DZXX

6/4/19

10

20

6/6/19

5278216

S25PG100DZXX

6/4/19

20

20

6/6/19

5278216

S25PG100DZXX

6/4/19

30

20

6/6/19

5278216

S25PG100DZXX

6/4/19

40

20

6/23/19

5278216

S25PG100DZXX

6/4/19

50

20

6/25/19

5278216

S25PG100DZXX

6/4/19

60

20

6/28/19

5278826

S25PG100DZXX

6/10/19

10

16

6/12/19

5278826

S25PG100DZXX

6/10/19

20

16

6/18/19

5278826

S25PG100DZXX

6/10/19

30

16

9/21/20

5278831

S25PG100DZXX

6/10/19

10

16

6/17/19

5278831

S25PG100DZXX

6/10/19

20

16

6/18/19

5278831

S25PG100DZXX

6/10/19

30

16

9/21/20

5280443

S25PG100DZXX

6/26/19

10

4

7/3/19

5280443

S25PG100DZXX

6/26/19

20

4

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @MSW ,

Try it.

Measure = var _a=CALCULATE(MAX('Table'[Complete Date]),FILTER(ALL('Table'),[ID]=MAX([ID])&& [EQ]<MAX('Table'[EQ])))
return 
IF(MAX('Table'[EQ])=10,DATEDIFF(MAX('Table'[StartDate]),MAX('Table'[Complete Date]),DAY),DATEDIFF(_a,MAX('Table'[Complete Date]),DAY))

The final show:

vyalanwumsft_0-1656924110840.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @MSW ,

Try it.

Measure = var _a=CALCULATE(MAX('Table'[Complete Date]),FILTER(ALL('Table'),[ID]=MAX([ID])&& [EQ]<MAX('Table'[EQ])))
return 
IF(MAX('Table'[EQ])=10,DATEDIFF(MAX('Table'[StartDate]),MAX('Table'[Complete Date]),DAY),DATEDIFF(_a,MAX('Table'[Complete Date]),DAY))

The final show:

vyalanwumsft_0-1656924110840.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

daXtreme
Solution Specialist
Solution Specialist

Hi @MSW 

 

Sorry but I have some problems to follow what it is you want. Could you please be more specific? Please refer to this article for ideas about how to make your question a good one: How to Get Your Question Answered Quickly (powerbi.com)

 

It's always good to place in here the starting point, the reasoning for achieving the final result and the final result itself. This will immensely increase the chances of addressing your problem.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors