Rolling 12 month Sum for prior 2 years

[AshDil]

Hi, 
I have 2 slicers one for year and other for month. If I have selected year as 2021 and month as January, then need to show rolling 12 month sum for periods,

Feb 2020 - Jan 2021

Feb 2019 - Jan 2020
Please help me to do.

Thanks,
AshDil.

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Total sales = sum(Data[sales])

Sales in last 12 months = calculate([total sales],datesbetween(calendar[date],edate(min(calendar[date])-11),max(calendar[date])))

Sales before = calculate([Sales in last 12 months],sameperiodlastyear(calendar[date]))

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Total sales = sum(Data[sales])

Sales in last 12 months = calculate([total sales],datesbetween(calendar[date],edate(min(calendar[date])-11),max(calendar[date])))

Sales before = calculate([Sales in last 12 months],sameperiodlastyear(calendar[date]))

Hope this helps.

[SanketBhagwat]

Hi @AshDil .

1)Create a measure for Total Sales as;
Total Sales=SUM(Sales).

2)Create a measure for rolling sum as ;
Rolling Sum for last 2 years= CALCULATE(
[Total Sales],
DATESINPERIOD(Table[Date],
MAX(Table[Date]),
-2,
YEAR)).

Pleast let me know if it works.

Regards,

Sanket Bhagwat.

[AshDil]

Hi @SanketBhagwat ,
If I have used above calculation I will get rolling sum for last 24 months i.e, FEB 2019 - JAN 2021, not for 12 month sum seperately.

I want to show 2 seperate 12 month sum for periods

Feb 2020 - Jan 2021

Feb 2019 - Jan 2020

Thanks,

AshDil.

[SanketBhagwat · Accepted solution]

Hi @AshDil .

In that case create 2 separate measures with little tweak in the same formula.

For last 12 months;

Rolling Sum for last year= CALCULATE(
[Total Sales],
DATESINPERIOD(Table[Date],
MAX(Table[Date]),
-1,
YEAR))

Rolling Sum before last 12 months=CALCULATE[Rolling Sum for last year],
SAMEPERIODLASTYEAR(Table[date]).

Please let me know if that works for you.

Regards,

Sanket Bhagwat