Cumulative total by selected time range

[Marcin]

Hi,

I would like to create 12 months cumulative total and present it on chart. 

The case is that when I use both Year and Month filters I would like the Cumulative Sales to be calculated as last 12 months before selected month , now I have only one month presented :

The definition of my cumulative measure : 

Cumulative Quantity =

IF (

MIN ( 'Date'[DateKey] )

<= CALCULATE ( MAX ( Sales[OrderDateKey] ), ALL ( Sales ) ),

CALCULATE (

[Distinct Sales],

FILTER (

ALL ( 'Date'[Date] ),

'Date'[Date] <= MAX ( 'Date'[Date] )

)

)

)

[Geradav]

Hi @Marcin 

Check out this video https://youtu.be/duMSovyosXE

That will give you exactly what you are looking for.

Does that work for you?

[Marcin]

Hi @Geradav ,

I saw this video before and it's presenting absolut values not cumulative so it;s not what I am looking for. 

[Geradav]

Hi @Marcin 

I actually followed the instruction in the video and adapted it to calculate a cumulative instead of absolute results and came up with the following

I created an independent calendar from which I created the slicers and retrieved the first date of the desired period. I did it over 3 months because my model is not big enough to go back 12 but here is the DAX statement for 12 months

Cumul =
VAR MinDate =
    EOMONTH ( MAX ( IndependentCalendar[Date] ), -12 ) + 1
RETURN
    CALCULATE (
        [Current Profit],
        DATESBETWEEN ( 'Sample Data'[Date], MinDate, MAX ( 'Sample Data'[Date] ) )
    )

Does that correspond to what you were looking for?