Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, I am having an issue with a cumulative line that is doing what it's intended to, but it's misleading when I view it in a chart. I'm tracking cumulative forecast miss as measured by Actual Spend - Forecasted Spend by month.
It's currently showing a huge cumulative miss in the months of November and December because we have forecasted spend but no actual spend yet in the month. I'd like to terminate the cumulative total at a specific point, but I'm not sure how to do that.
I could make any of the following work (ranked in order of what I'd prefer).
1. Terminate the line at the date where I no longer have actual spend.
2. Manually maintain a table to show which months are considered "Actual Spend"
3. Key in a date within a formula to terminate the cumulation based on that date.
4. Literally any other mechanism to get this done...
The two formulas I'm currently using are below:
Monthly CSB Delta =
sum('CSB Financials Monthly Summary'[Monthly Actual])-sum('CSB Financials Monthly Summary'[Monthly Forecast])
Cumulative CSB Project Delta =
SUMX ( FILTER ( ALLSELECTED ('CSB Financials Monthly Summary'), 'CSB Financials Monthly Summary'[Period Date]<=max('CSB Financials Monthly Summary'[Period Date]) ), [Monthly CSB Delta] )
I'm open to any solution, so if I need to add in other fields within the tables, I'm happy to do that, I just need something that'll work. Thanks for your help!
Kyle Vonck
Solved! Go to Solution.
Hi @vonckk ,
You could use IF() to filter out the case where Actual is not blank:
Monthly CSB Delta =
IF (
MAX ( 'CSB Financials Monthly Summary'[Monthly Actual] ) <> BLANK (),
SUM ( 'CSB Financials Monthly Summary'[Monthly Actual] )
- SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] ),
0
)
Or just use the following formula:
Measure =
SUM ( 'CSB Financials Monthly Summary'[Monthly Actual] )
- SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] )
+ CALCULATE (
SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] ),
FILTER (
'CSB Financials Monthly Summary',
'CSB Financials Monthly Summary'[Monthly Actual] = BLANK ()
)
)
My final output looks like this:
If I answered your queation, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Eyelyn Qin
Hi @vonckk ,
Sorry to disturb you...
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.
Best Regards,
Eyelyn Qin
Hi @vonckk ,
You could use IF() to filter out the case where Actual is not blank:
Monthly CSB Delta =
IF (
MAX ( 'CSB Financials Monthly Summary'[Monthly Actual] ) <> BLANK (),
SUM ( 'CSB Financials Monthly Summary'[Monthly Actual] )
- SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] ),
0
)
Or just use the following formula:
Measure =
SUM ( 'CSB Financials Monthly Summary'[Monthly Actual] )
- SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] )
+ CALCULATE (
SUM ( 'CSB Financials Monthly Summary'[Monthly Forecast] ),
FILTER (
'CSB Financials Monthly Summary',
'CSB Financials Monthly Summary'[Monthly Actual] = BLANK ()
)
)
My final output looks like this:
If I answered your queation, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Eyelyn Qin
Hi,
Try this measure
=if(isblank(sum('CSB Financials Monthly Summary'[Monthly Actual])),blank(),SUMX ( FILTER ( ALLSELECTED ('CSB Financials Monthly Summary'), 'CSB Financials Monthly Summary'[Period Date]<=max('CSB Financials Monthly Summary'[Period Date]) ), [Monthly CSB Delta] ) )
Hope this helps.
Hey Ashish,
I've tried this and it's still working showing the delta for the full year. The dates in the formulas are not from a date table, does that change things?
Thanks,
Kyle
Hi,
I will need to see the file. Share the download link. RIght next to that faulty visual, in a text box, please explain the exact problem that you want to solve.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |