Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dates Between calculation based on previous row

Hi team,

 

I need help with a DatesBetween DAX calculation. I have data coming from two tables:
Table 1:

CodeKey                   

CodeNum

1  

PM033

2  

PM034

3  

PM035


Table 2:

CodeKey

Date

1   

19/01/2022

1   

11/02/2022

1   

07/03/2022

1   

21/03/2022

2   

1/21/2022

2

2/20/2022

2   

3/10/2022

3   

1/28/2022

3   

2/25/2022

 

I need the calculation to give me the number of dates between the date in the cell and the date from the previous row(date), based on CodeNum.

 

Final table – needed results:

CodeKey

CodeNum

Date (mm-dd-yyyy)

Dates between

1

PM033

1/19/2022

 

1

PM033

02/11/2022

23

1

PM033

03/07/2022

24

1

PM033

03/21/2022

14

2

PM034

1/21/2022

 

2

PM034

2/20/2022

30

2

PM034

3/10/2022

18

3

PM035

1/28/2022

 

3

PM035

2/25/2022

28


Results explanation: If the Codenum is PM033, I need next to 02/11/2022 to see the number of dates between 02/11/2022 and 1/19/2022 (previous row); if CodeNum = PM034 in the column next to 3/10/2022, I need the number of days between 2/20/2022 and 3/10/2022.

 

There are many CodeNum-s, so it cannot be hard-coded.

 

Thank you.

 

 

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Days between measure: =
VAR currentcode =
    MAX ( Table1[CodeKey] )
VAR currentdate =
    MAX ( Table2[Date] )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Table2 ),
            Table2[CodeKey] = currentcode
                && Table2[Date] < currentdate
        ),
        Table2[Date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[CodeKey] ),
        INT ( currentdate - previousdate )
            * DIVIDE ( previousdate, previousdate )
    )

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jihwan_Kim ,

    Thank you very much! This works perfectly. One more question, regarding the first row of my data, what should I add to the calculation in order the first row for 2022 to take the previous date from 2021?

    Best,
    Emma

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Days between measure: =
VAR currentcode =
    MAX ( Table1[CodeKey] )
VAR currentdate =
    MAX ( Table2[Date] )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Table2 ),
            Table2[CodeKey] = currentcode
                && Table2[Date] < currentdate
        ),
        Table2[Date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[CodeKey] ),
        INT ( currentdate - previousdate )
            * DIVIDE ( previousdate, previousdate )
    )