cancel
Showing results for
Did you mean:
Highlighted
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.

Thanks,

Paruchuri

1 ACCEPTED SOLUTION

Accepted Solutions
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] ) )
)
9 REPLIES 9
Helper I

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

Do you also have a year column?

Resolver I

No

Helper I

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

Then i don't think that's possible

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.

Microsoft

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

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

It doesn't helps me out.

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
Microsoft

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

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors