Issues with SumX and dates

[vonckk]

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

[v-eqin-msft]

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

[v-eqin-msft · Accepted 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:

Here is the pbix.

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

[Ashish_Mathur]

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.

[vonckk]

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

[Ashish_Mathur]

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.

[AllisonKennedy]

Are you looking for something like this post: https://radacad.com/stop-dax-cumulative-total-calculation-in-power-bi/?ref=818

It looks like there's a typo in the code in my browser on that blog post code, so here's a fixed example:

YTD Sales - stop last date =
var _lastActualValue = MAX(FactInternetSales[OrderDateKey])
return
IF(
SELECTEDVALUE(DimDate[DateKey])<=_lastActualValue,
CALCULATE(SUM(FactInternetSales[SalesAmount]),
DATESYTD(DimDate[FullDateAlternateKey]))
)

Note this solution assumes you have a Dim Date table related to your Financials Monthly Summary, so I'd suggest adding a Period Date Key and Dim Date table if you don't have them already:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html