cancel
Showing results for
Did you mean:
Member

## 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.

Thanks,

Paruchuri

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## 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
Regular Visitor

## Re: Creating a Rolling 12 months using Month Column

Do you also have a year column?

Member

No

Regular Visitor

## Re: Creating a Rolling 12 months using Month Column

Then i don't think that's possible

Member

## 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.

Super Contributor

## Re: Creating a Rolling 12 months using Month Column

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.
Member

## Re: Creating a Rolling 12 months using Month Column

It doesn't helps me out.

Highlighted
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
Super Contributor

## Re: Creating a Rolling 12 months using Month Column

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.
Member

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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 322 members 3,311 guests
Recent signins: