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] ) )
    )
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi  Anonymous

 

You may try with below measure. Attached the sample file. If it is not your case, please share some sample data.

Measure =
CALCULATE (
    SUM ( Data[Sales] ),
    FILTER (
        ALL ( 'Data' ),
        'Data'[YearMonth]
            >= MAX ( Data[YearMonth] ) - 11
            && 'Data'[YearMonth] <= MAX ( 'Data'[YearMonth] )
    )
)

Regards,

Cherie

 

Anonymous's avatar
Anonymous
Not applicable
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] ) )
)