cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

 

Thanks,

Paruchuri

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Creating a Rolling 12 months using Month Column

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] ) )
)
9 REPLIES 9
Highlighted
NickolajJessen Regular Visitor
Regular Visitor

Re: Creating a Rolling 12 months using Month Column

Do you also have a year column?

Re: Creating a Rolling 12 months using Month Column

No

NickolajJessen Regular Visitor
Regular Visitor

Re: Creating a Rolling 12 months using Month Column

Then i don't think that's possible

Re: Creating a Rolling 12 months using Month Column

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.

 

Community Support Team
Community Support Team

Re: Creating a Rolling 12 months using Month Column

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

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Creating a Rolling 12 months using Month Column

It doesn't helps me out. 

NickolajJessen Regular Visitor
Regular Visitor

Re: Creating a Rolling 12 months using Month Column

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
Community Support Team
Community Support Team

Re: Creating a Rolling 12 months using Month Column

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

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Creating a Rolling 12 months using Month Column

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