Change cumulative sales total to ignore date filter (example file in comments)

Power BI
1

Hi all,

How do I change the following DAX for cumulative sales total to ignore the report’s date filter and give the last 12 months.

Code:

Cumulative Sales =
CALCULATE(
[Sales Total],
FILTER(
ALLSELECTED( ‘Dates’ ),
‘Dates’[Date] <= MAX( ‘Dates’[Date])
)
)

resulting chart looks like this

image

Which is what I’m after but I need to ignore the date filter to give the cumulative sales for the last 12 months.

So the resulting chart is the same as above but for October - September regardless of date filter.

Not sure if more info is needed or a sample file, please let me know.

Tim

2

Hi Tim,

Try ALL instead of ALLSELECTED.

ALL will ignore date filter. ALLSELECTED will update for the date filter.

3

Hi Kim,

Funny enough that doesn’t change the chart at all. The mysteries of ALL and ALLSELECTED…

I’ve uploaded a file with the chart
Mock Data PBI.pbix (672.8 KB)

Thanks,
Tim

4

Hi Timmay,

Your measures are actually working as intended, the problem is you need to edit the slicer interactions with the graph.

Highlight the slicer, from the ribbon select Format/Edit Interactions. the select None circle above graph

image
image1350×908 64.9 KB

I am trying to fix the measure so it doesn’t show months in the future but I need to get some work done but will revisit later after work

5

@Timmay ,

I happened to jump on this one at the same time as @KimC and took a different approach, although hers is completely valid as well - always just multiple ways of doing things in Power BI. I used a disconnected Dates table, rather than addressing it through the visual interactions, and also used the monthly offsets to step backward by 12 months. Here’s the measure that does the heavy lifting:

Cumul Sales Last 12M = 

VAR FYMOffset = 
LOOKUPVALUE( Dates[MonthOffset], Dates[Date], [Harvest Max DisconnDate] )

VAR Result =
CALCULATE(
    [Sales Total],
    FILTER( 
        ALL( Dates ),
        Dates[Date] <= MAX( Dates[Date] ) &&
        Dates[MonthOffset] <= FYMOffset &&
        Dates[MonthOffset] >  FYMOffset -12
    )
)

VAR FiltResult =
IF( SELECTEDVALUE( Dates[MonthOffset] ) <= FYMOffset &&
        SELECTEDVALUE( Dates[MonthOffset] ) >  FYMOffset -12,
        Result,
        BLANK()
)

RETURN FiltResult

And here’s how it looks all put together:

image
image900×556 35.6 KB

I hope this is helpful. Full solution file attached below.

  • Brian

eDNA Forum - Mock Data PBI Solution.pbix (748.1 KB)

2 Likes
6

Works perfect thank you.

7

@Timmay,

Good deal. Glad that worked well for you. The offset functions in @Melissa 's Extended Date table are a marvel.

  • Brian
8

Great solution from Brian which got me thinking…I like the measure approach instead of edit interactions.
I modified my formula to be cumulative from inception and excluded future dates

image
image