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,
I'm currently working on something where i have to calculate cumulative totals, where i have to check if a date from my calendar table falls in a certain date range of an other table. And if that is the case i have to calculte for each day how many id's there are per version. So far i haven't find the correct DAX formula to calulate it. I have put some dummy data down below
table IDVersion
ID Version StartDate EndDate
1 7.0 2003-07-02 2004-05-05
2 8.0 2013-10-09 2016-03-03
3 7.1 2005-06-07 2008-09-07
4 7.0 2004-03-13 2005-10-21
5 8.0 2014-12-24 2015-07-11
... ... ... ...
tabel Calendar
ID Date
1 2003-01-01
2 2003-01-02
3 2003-01-03
4 2003-01-04
5 2003-01-05
... ...
Result (Table Visual in Power BI)
Date Version CumulativeIDs (measure)
2003-07-02 7.0 1
2003-07-03 7.0 1
2003-07-04 7.0 1
. . .
. . .
. . .
2004-03-13 7.0 2
2004-03-14 7.0 2
Solved! Go to Solution.
Hi @Anonymous
Firstly, create calendar table named Calendar, and create measure CumulativeIDs.
Calendar = CALENDAR("2003,01,01",TODAY())
CumulativeIDs =
var d=SELECTEDVALUE('Calendar'[Date])
var v=SELECTEDVALUE(IDVersion[Version])
return COUNTROWS(FILTER(IDVersion,IDVersion[Version]=v&&d<=IDVersion[EndDate]&&d>=IDVersion[StartDate]))
Then, choose table visual to display the result.
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUQiegXq7-ZNhJtpjT0MqSgByeUbs5njxIYU8h3-ME6wFw?e=8S5PWu
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Firstly, create calendar table named Calendar, and create measure CumulativeIDs.
Calendar = CALENDAR("2003,01,01",TODAY())
CumulativeIDs =
var d=SELECTEDVALUE('Calendar'[Date])
var v=SELECTEDVALUE(IDVersion[Version])
return COUNTROWS(FILTER(IDVersion,IDVersion[Version]=v&&d<=IDVersion[EndDate]&&d>=IDVersion[StartDate]))
Then, choose table visual to display the result.
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUQiegXq7-ZNhJtpjT0MqSgByeUbs5njxIYU8h3-ME6wFw?e=8S5PWu
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai,
Your solution helped me a lot, i can now you use this measure to make another one that allows me to visualize this in a line chart. Thanks for your help!
Best regards,
@Anonymous
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 |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |