Dates Between calculation based on previous row

[Anonymous]

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.

[Anonymous]

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 · Accepted solution]

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