Desktop

[paruchuri447]

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.

Thanks,

Paruchuri

[paruchuri447 · Accepted solution]

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]

Do you also have a year column?

[paruchuri447]

No

[NickolajJessen]

Then i don't think that's possible

[paruchuri447]

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.

[v-cherch-msft]

Hi @paruchuri447

You may have a look at below posts. Sample data and expected output will be helpful to provide an accurate solution.

https://community.powerbi.com/t5/Desktop/Moving-Average-for-Prev-6-Next-6-Months/td-p/171712

https://community.powerbi.com/t5/Desktop/Averages-For-The-Last-12-Months/td-p/324289

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Regards,

Cherie

[paruchuri447]

It doesn't helps me out. 

[NickolajJessen]

If you have Year and month, you can use the function 'DATE'

Date = DATE(Tabel3[Year];[Month];1)

When you have your date column, you can use this link for the rest
https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/td-p/164419

[v-cherch-msft]

Hi  @paruchuri447

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

Creating a Rolling 12 months using Month Column.pbix

[paruchuri447 · Accepted solution]

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] ) )

)