CODEWITHSUNDEEP
exceldatepowerpivotdaxrolling-sum
Ahmad AB
Ahmad AB

Reputation: 45

Rolling 12 Month sum in PowerPivot

In PowerPivot Excel 2016 I write a formula for rolling 12 month sum of sales as below :

Rolling Sum:=CALCULATE (
[Sales] ,
DATESBETWEEN (
    Sales[Date],
    FIRSTDATE(DATEADD(Sales[Date],-365,DAY)),
    LASTDATE (Sales[Date] )
 )
)

But it seems not working correctly. for each month it shows me only sales of that month! Does anybody knows how should I fix my problem?!

Thanks In Advance

Upvotes: 2

Views: 3753

Answers (2)

cloudcell
cloudcell

Reputation: 497

Using fixed 365 days does not properly account for leap days. In the following code, "[CurDatePerCalendar]" measure contains the date for which you want to calculate the average. 

Sales TTM = 
VAR base = [CurDatePerCalendar]
VAR StartDate = EDATE(base,-12) + 1
VAR EndDate = base
RETURN
CALCULATE(
    [SalesSum],
    FILTER ( _Calendar,
    _Calendar[Date] >= StartDate
    && 
    _Calendar[Date] <= EndDate )
)

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

If you don't have a Date/Calendar table you can't use Time Intelligence functions properly.

Despite the best practice would be have a Calendar/Date table and use Time Intelligence functions, you can get the desired result by using an explicit filter:

Rolling Sum :=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( Sales ),
        [Date]
            >= MAX ( Sales[Date] ) - 365
            && [Date] <= MAX ( Sales[Date] )
    )
)

Let me know if this helps.

Upvotes: 0

Related Questions