How to get rolling 12 month sum in PowerBI

0
votes

I have to create sum of 12 month sales from last month of my date column.

I have 3 years, 2016 to 2018 with Month and Year format and my dashboard is refreshed monthly adding next month.

If the last date is oct 2018,

I want to display 2018 as sum of sales from oct 18 - oct 17 2017 as sum of sales from oct 17 to oct 16 2016 as sum of sales from oct 16 to oct 15 and so on.

Can you help how can i create the dax measure in power BI

If I am using = Calculate(sum(sales), dateadd(date, -12, month) It is displaying only year wise (calculating oct18 to jan 18 only) and not giving me the result as mentioned above.

Can you help with the same?

2
powerbidax

2 Answers

2
votes

Assuming you have a date/calendar table called 'Date', you can achieve a rolling 12 month sum using the following measure:

Rolling 12 Month Total :=
CALCULATE (
    SUM ( 'Table'[Sales] ),
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

Applying this to other years would just require you to change the bounds in the DATESBETWEEN function.

0
votes

be carefull with February you will have an isue. when the last day of current year is 28th and previous year is 29th.

The right formula is Rolling 12 Month

Total :=
CALCULATE (
    SUM ( 'Table'[Sales] ),
    DATESBETWEEN (
        'Date'[Date],
         SAMEPERIODLASTYEAR ( NEXTDATY(LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)