Forum Discussion

jvandyck's avatar
jvandyck
Icon for Helper IV rankHelper IV
5 years ago
Solved

rolling 12 month total

Hi    I am not able to figure this one out. I am trying to create a rolling 12 month total and I have a formula like this: Value 12 rolling = Var _MaxDate=max('Date'[Date]) Return calculate(s...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    jvandyck , Try with help from date table , using examples

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

jvandyck , Try with help from date table , using examples

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
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.

  • jvandyck's avatar
    jvandyck
    Icon for Helper IV rankHelper IV
    5 years ago

    Thanks for you extremely quick answer!!!!

  • jvandyck's avatar
    jvandyck
    Icon for Helper IV rankHelper IV
    5 years ago

    Thank you for your extremely quick answer!