Forum Discussion
Cumulative total with dates that do not have values
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?
12 Replies
- Ashish_Mathur
Super User
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?
- Ashish_Mathur
Hi,
In PowerBI desktop, click on the measure on the right hand side pane and you will see the measure in the formula bar.
Seward12533Solution Sage
Your close need to specifybtbe date table name as a term in the calculate to force the filter context to between the two tables.
Total_to_date:=calculate(counta(tblWorkStatus[Work_status]),tblWorkStatus[Work_status]=3,tbleDate,filter(all(tblDate[date]),tblDate[date]<=max(tblWorkStatus[date])))Thanks for the input Seward. When I tried your formula, it gave me the same values as "Count of Work Status".
