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.
I'm looking to create a measure that counts cumulative totals and just cannot seem to find the right solution. I have a table called "tblWorkStatus" that looks like this:
Work_status | date |
1 | 2018-11-01 |
2 | 2018-06-01 |
3 | 2018-12-01 |
4 | 2018-06-01 |
5 | 2018-05-01 |
3 | 2018-10-01 |
3 | 2018-06-01 |
3 | 2018-03-01 |
3 | 2018-06-01 |
3 | 2019-11-01 |
3 | 2018-02-01 |
3 | 2018-03-01 |
3 | 2018-05-01 |
3 | 2018-12-01 |
I have another table called tblDate that has the first of the month dates for all dates between 2018-01-01 and 2019-12-01.
Date | Month |
2018-01-01 | 1 |
2018-02-01 | 2 |
2018-03-01 | 3 |
2018-04-01 | 4 |
2018-05-01 | 5 |
2018-06-01 | 6 |
2018-07-01 | 7 |
2018-08-01 | 8 |
2018-09-01 | 9 |
2018-10-01 | 10 |
2018-11-01 | 11 |
2018-12-01 | 12 |
2019-02-01 | 2 |
2019-03-01 | 3 |
2019-04-01 | 4 |
2019-05-01 | 5 |
2019-06-01 | 6 |
2019-07-01 | 7 |
2019-08-01 | 8 |
2019-09-01 | 9 |
2019-10-01 | 10 |
2019-11-01 | 11 |
2019-12-01 | 12 |
I'd like to have a running cumulative total of all work status "3". I've created a measure called "Total_to_Date", but what I really desire is "what_I_want".
Total_to_date:=calculate(counta(tblWorkStatus[Work_status]),tblWorkStatus[Work_status]=3,filter(all(tblDate[date]),tblDate[date]<=max(tblWorkStatus[date])))
Dates | Count of Work Status | Total_to_Date | what_I_want |
2018-01-01 | 0 | ||
2018-02-01 | 1 | 1 | 1 |
2018-03-01 | 2 | 3 | 3 |
2018-04-01 | 3 | ||
2018-05-01 | 1 | 4 | 4 |
2018-06-01 | 2 | 6 | 6 |
2018-07-01 | 6 | ||
2018-08-01 | 6 | ||
2018-09-01 | 6 | ||
2018-10-01 | 1 | 7 | 7 |
2018-11-01 | 7 | ||
2018-12-01 | 2 | 9 | 9 |
2019-02-01 | 9 | ||
2019-03-01 | 9 | ||
2019-04-01 | 9 | ||
2019-05-01 | 9 | ||
2019-06-01 | 9 | ||
2019-07-01 | 9 | ||
2019-08-01 | 9 | ||
2019-09-01 | 9 | ||
2019-10-01 | 9 | ||
2019-11-01 | 1 | 10 | 10 |
2019-12-01 | 10 |
I can't seem to figure out how to get my calculated measure to fill in total values if there is no corresponding work status for a given month. I've pored over several posts on the forum to no avail. Any help?
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
This certainly has the behavior that I'm looking for. I downloaded the PBIX file and am viewing with the web interface, but cannot seem to find the calculation for the "YTD work status count" field. What is the formula for the calculation?
Hi,
In PowerBI desktop, click on the measure on the right hand side pane and you will see the measure in the formula bar.
Unfortunately for me, my workplace hasn't sprung for PowerBI desktop, so I am limited to the web interface and using PowerPivot in Excel. So I can't confirm if the proposed solution works.
Aha! Thanks Seward! Clearly my greenhorn is showing.
So did my solution work?
Yes, elegantly. Thanks!
Thank you for confirming. Please mark my response as Answer.
I think I have done so? I did select "Accept as solution", and when I click on "Go to solution" in the original question it takes me to your solution. As I mentioned I am pretty new to this so it is entirely possible that I didn't complete the process. Don't hesitate to let me know if there is a further step. Thanks again!
Thanks for the input Seward. When I tried your formula, it gave me the same values as "Count of Work Status".
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |