Forum Discussion

Anna422173's avatar
Anna422173
Regular Visitor
3 years ago
Solved

Rolling sum R12 with filter View

Hello,   I would like to have a 12 month rolling sum for revenue. This measure should be used in a view, which shows only a date range (example 01.11.2022 - today) The measure should sum up also ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anna422173 , Make sure you are using date table, marked as date table, single directional join. Field from date table need to used in visual, measure and slicer

     

    you need a measure like

     

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

     

    using window for 12 month not by days

     

    Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

     

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    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.

     

amitchandak's avatar
amitchandak
Super User
3 years ago

Anna422173 , Make sure you are using date table, marked as date table, single directional join. Field from date table need to used in visual, measure and slicer

 

you need a measure like

 

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

 

using window for 12 month not by days

 

Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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.