Getting my cumulative total to stop

[Anonymous]

Hi all

I've spent a day or two exercising my Google-fu but haven't found an iteration that works. I have a cumulative forecast measure (that works) and I've applied a relative date filter that stops the graph showing past the next six months. I want to add the cumulative actuals but I need them to stop at the last date that has an actual, not keep going with a flat line.

My cumulative forecast formula (which works) is: 

Cumulative Forecast = CALCULATE([Forecast Logs (+5%)], FILTER(ALLSELECTED(DateTable), DateTable[Date] <= MAX(DateTable[Date])))

My cumulative actual forecast (which technically works, just keeps going that is all) is:

Cumulative Actual = CALCULATE([Total Tonnes], FILTER(ALLSELECTED(DateTable), DateTable[Date] <= MAX(DateTable[Date])))

My data model is simple; one table of data, and one date table. I have included a Week Ending date which is what I want to graph this on.

Including some sample data and the screenshot of what I have so far. Would really appreciate any guidance or shared wisdom, please!

DateFrom	DateTo	Tonnes
20/03/2021 14:10	24/03/2021 7:00	7685.59
15/03/2021 19:12	19/03/2021 23:55	35367.23
14/03/2021 16:00	16/03/2021 8:20	11786.17
14/03/2021 9:24	17/03/2021 20:40	24553.34
12/03/2021 15:12	14/03/2021 9:30	9304.24
9/03/2021 7:50	13/03/2021 0:11	22591.34
7/03/2021 6:15	9/03/2021 6:40	17996.99
6/03/2021 11:55	9/03/2021 7:10	8930.37
3/03/2021 9:36	4/03/2021 16:40	8407.17
1/03/2021 22:00	4/03/2021 13:15	14951.53
27/02/2021 5:18	3/03/2021 10:15	24457.42
27/02/2021 0:00	28/02/2021 0:00	3981.99
26/02/2021 18:54	28/02/2021 8:29	12017.25
26/02/2021 0:00	27/02/2021 0:00	200.19
25/02/2021 0:00	26/02/2021 0:00	780.49
24/02/2021 0:00	25/02/2021 0:00	4250.78
23/02/2021 16:48	26/02/2021 15:15	28191.76
23/02/2021 0:00	24/02/2021 0:00	4944.33
20/02/2021 10:55	22/02/2021 5:24	7948.28
19/02/2021 0:00	21/02/2021 0:00	6448.36
17/02/2021 12:00	20/02/2021 0:22	15697.09
17/02/2021 0:00	18/02/2021 0:00	3098.36
16/02/2021 18:30	19/02/2021 8:14	21074.06
16/02/2021 0:00	16/02/2021 0:00	88.64
15/02/2021 0:00	15/02/2021 0:00	3905.68
14/02/2021 0:00	15/02/2021 0:00	2987.78
13/02/2021 0:00	14/02/2021 0:00	2708.24
12/02/2021 23:24	15/02/2021 20:05	19097.89
11/02/2021 1:37	13/02/2021 17:43	23409.93
11/02/2021 0:00	11/02/2021 0:00	1047.6

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi Ashish, my apologies but I can't due to it being commercially sensitive. Was hoping the sample data would be useful. It is a basic data set, only the two tables in the model. 

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work

Cumulative Actual = if(isblank([Total Tonnes]),blank(),CALCULATE([Total Tonnes], FILTER(ALLSELECTED(DateTable), DateTable[Date] <= MAX(DateTable[Date]))))

Hope this helps.

[Anonymous]

Thats it! Thank you so much. I had tried something similar but obviously didn't have the syntax quite right. Appreciate your time

[Ashish_Mathur]

You are welcome.

[AllisonKennedy]

@Anonymous  What you have is technically correct, but if you want it to stop you can add an IF statement to check if the date is before today:

New Measure= IF(DateTable[Date] < TODAY(), BLANK(), [Cumulative Actual])

[Anonymous]

Hi Allison, thank you for that. I may be being a bit of a numpty but I can't create a dax formula with IF and that context in the measure, it won't pick up my date table (I can confirm the relationship is working) so I suspect I need to wrap in in something else.