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 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:
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 |
Solved! Go to 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.
Hi,
Share the link from where i can download your PBI file.
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.
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.
Thats it! Thank you so much. I had tried something similar but obviously didn't have the syntax quite right. Appreciate your time
You are welcome.
@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])
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |