cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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
Highlighted
Resolver I
Resolver I

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

View solution in original post

9 REPLIES 9
Highlighted
Helper I
Helper I

Re: Creating a Rolling 12 months using Month Column

Do you also have a year column?

Highlighted
Resolver I
Resolver I

Re: Creating a Rolling 12 months using Month Column

No

Highlighted
Helper I
Helper I

Re: Creating a Rolling 12 months using Month Column

Then i don't think that's possible

Highlighted
Resolver I
Resolver I

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.

 

Highlighted
Microsoft
Microsoft

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.
Highlighted
Resolver I
Resolver I

Re: Creating a Rolling 12 months using Month Column

It doesn't helps me out. 

Highlighted
Helper I
Helper I

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
Highlighted
Microsoft
Microsoft

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.
Highlighted
Resolver I
Resolver I

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

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors