Cumulative total over the last 12 month - month on month

[Pandetbaby]

Hey!

I have a date filter that lets me select a date, and I want to find the cumulative from that date + last 11 months

Using the table below, Id like to pick Jan 2021 and see 11 as the cumlative total (Feb 2020 + Mar 2020+ Jun 2020 + Jan 2021)

Whilst if I pick Feb 2021 I would see 8  ( Mar 2020 + Jun 2020, Jan 2021 + Feb 2021)

Date	Instance
Jan 2020	1
Feb 2020	4
Mar 2020	2
Jun 2020	3
Jan 2021	2
Feb 2021	1

Is there a Dax formula that could easily solve this?

Thanks!

[vishal097]

*Rolling Cummulative Total for last 3 Year based on Selected Year*

I have scenario to show “cumulative total in each year” so that if any month is selected then it will show sum till that month only in that year.

we can get "cumulative total in each year" by DatesYTD and it want based on month filter also

But then I want to show this result for last 3 year based on current year selected in year filter.

I have written measure like 

Cumulative Rolling KPI Score =

Var ScoreYTD =

CALCULATE( SUM( 'KPI Fact'[KPI Actual] ), DATESYTD( DimDate[Date] ))

Var Result =

CALCULATE( ScoreYTD ,

DATESINPERIOD(DimDate[Date], MAX( DimDate[Date]) , -3, YEAR )

Return

Result

But this show result only for 1 year instead of for last three year.

Expected result

Based on current year select

Getting result as

[amitchandak]

@Pandetbaby , You can get rolling 12 like the example below with help from date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.