Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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 )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |