Cumulative total with dates that do not have values

[MSaunders206]

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?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[MSaunders206]

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.

[MSaunders206]

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. 

[Seward12533]

You mean PowerBI service? PowerBI desktop is free.

You should be able to download the. PBIX and copy the DAX

You can do 95% of what you can do in PBI in PowerPivot and many people develop the data models in PowerPivot and then transfer to PowerBI.

[MSaunders206]

Aha!  Thanks Seward!  Clearly my greenhorn is showing. 

[Ashish_Mathur]

So did my solution work?

[MSaunders206]

Yes, elegantly.  Thanks!

[Ashish_Mathur]

Thank you for confirming.  Please mark my response as Answer.

[MSaunders206]

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!

[Seward12533]

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

[MSaunders206]

Thanks for the input Seward.  When I tried your formula, it gave me the same values as "Count of Work Status".