Cumulative totals not affected by Month Selections

DAX DAX Calculations
1

Hi everyone,

I am writing Cumulative Overead measures for current year (2019) vs Budget vs Last Year. The DAX are as follows:

CUMULATIVE OVERHEAD = 
Var LastCashTransactionDate = CALCULATE( MAX( CashFlowTransactions[Date] ), ALL( CashFlowTransactions ) )
Return
if( SELECTEDVALUE( 'Calendar'[Date] ) > LastCashTransactionDate,
    BLANK(),
    CALCULATE(
        [Total Overhead],
        FILTER(
            ALLSELECTED( 'Calendar'),
            'Calendar'[Date] <= MAX( 'Calendar'[Date] )
        )
    )
)


CUMULATIVE OVEREAD BUDGET = 
CALCULATE (
    [Total Overhead Budget],
    FILTER( ALLSELECTED( 'Calendar' ),
        'Calendar'[Date] <= MAX( 'Calendar'[Date] )
    )
)


CUMULATIVE OVERHEAD LY = 
CALCULATE(
    [Total Overhead LY],
    FILTER( ALLSELECTED( 'Calendar' ),
        'Calendar'[Date] <= MAX( 'Calendar'[Date] )
    )
)

If all the months are selected, then the cumulative chart shows correctly: Budget and LY shows for all months until December.

image

Now when I select certain months, for example January - March, it filters the Actual to March which is correct. However, the problem is Budget and LY are filtered to March only as well, while I want Budget and LY always show for all the months until December, as there are data for the full year for Budget and LY data.

image

image

Could anyone please advise how to modify the Cumulative Budget and Cumulative LY measures above to ignore the month selection to always show full year data? (until Dec instead of March in the above screenshot example).

PS: Sorry for the long post as I do not have a simplified data model to share.

Thank you.

2

With the way you have this setup currently you can’t unfortunately ignore the slicer and the context on the page which is being created by the slicer selection.

You have to basically adjust this in the model if you want something similar to what you’re describing

I’ve setup a similar scenario to your below and getting similar results.

image
image.png873×528 30.1 KB

If I want to only change the actuals results (as in the cumulative total for actuals) I need to somehow create a filter within the formula so only the result changes vs the entire report page.

To do this I’m going to create a new date table.

image

I’m also not going to create any relationship here from this table to anything. I will consider this just a supporting table.

image
image.png1031×684 24.5 KB

image

image

Now I need to integrate this into my formula (or create a new one - this is probably best for something unique like this)

First…

Max Filtered Date = 
MAX( 'Filter Dates'[Date] )

image

Then…

Filtered Cumulative Sales = 
IF( HASONEVALUE( Dates[Date] ),
    IF( VALUES( Dates[Date] ) < [Max Filtered Date], [Cumulative Sales], BLANK() ),
        BLANK() )

image
image.png890×460 22.7 KB

See how you go with these ideas.

Thanks
Sam

1 Like