## Desktop

Member
Posts: 49
Registered: ‎11-18-2017

# 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

Accepted Solutions
Member
Posts: 49
Registered: ‎11-18-2017

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

All Replies
Regular Visitor
Posts: 18
Registered: ‎11-28-2018

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

Do you also have a year column?

Member
Posts: 49
Registered: ‎11-18-2017

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

No

Regular Visitor
Posts: 18
Registered: ‎11-28-2018

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

Then i don't think that's possible

Highlighted
Member
Posts: 49
Registered: ‎11-18-2017

## 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
Posts: 1,097
Registered: ‎07-25-2018

## 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
Posts: 49
Registered: ‎11-18-2017

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

It doesn't helps me out.

Regular Visitor
Posts: 18
Registered: ‎11-28-2018

## 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
Posts: 1,097
Registered: ‎07-25-2018

## 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
Posts: 49
Registered: ‎11-18-2017

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