Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a Rolling 12 months using Month Column

Hi Community,   I have to create measure for calculating a rolling 12 months for Sales. I don't have date column but i have month colum(like 1,2,3 ..12) . How can i acheive it.   Please help.   ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    I have created a calender table and created a column order using ranx function. below is the working DAX. 
     
    12M Rolling YTD = IF (
    HASONEVALUE ( 'Calendar'[YearMonthNo] ),
    CALCULATE (
    SUM( 'NA Daily'[Total Sales] ),
    FILTER (
    ALL ( 'NA Daily'[Order] ),
    'NA Daily'[Order]
    >= SELECTEDVALUE ( 'Calendar'[Order_Calendar] ) - 11
    &&'NA Daily'[Order] <= SELECTEDVALUE ( 'Calendar'[Order_Calendar] )
    )
    ),
    CALCULATE ( ( 'NA Daily'[Total Sales YTD] ) )
    )
NickolajJessen's avatar
NickolajJessen
Solution Sage
7 years ago

Do you also have a year column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    No

    • NickolajJessen's avatar
      NickolajJessen
      Solution Sage
      7 years ago

      Then i don't think that's possible

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I have brought Year column and now i have concatenated Year and month column and created calculated column. Now i have the column like 201801.

         

        How can i get the rolling 12 months.