12 month rolling average with date hierarchy

[Anonymous]

Hi,

I want to show a graph of rolling 12 month trend from 2021 to 2022. 

My data is as follow: 

	Sales	Rolling 12 month
Jan-21	15,740	20,916
Feb-21	22,108	22,034
Mar-21	19,559	21,390
Apr-21	16,361	20,677
May-21	21,038	20,517
Jun-21	18,152	20,054
Jul-21	16,546	19,644
Aug-21	22,628	19,511
Sep-21	23,422	19,761
Oct-21	27,132	20,378
Nov-21	23,814	20,488
Dec-21	19,453	20,496
Jan-22	18,577	20,733

Example of outcome : 

What i get in Powerbi, unable to combine two years. 

Correct graph to display, using excel

Problem: I am using date hierarchy. My other charts are heavily rely on date hierarchy and I do not wish to delete it. How do I create graph of rolling trend with date hierarchy? Thanks.

[Anonymous]

Hi @Anonymous ,

Please put date hierarchy in axis field, sales to column value and rolling average to line value.

Then drill down to the lowest level of data.

Result would be shown as below:

Best Regards,

Jay

[Anonymous]

Hi, 

Thanks for your advice! How do I filter only 12 months in this graph? I want remove Jan 2021 and Feb 2022 onwards.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I guess you want dynamic filter last 12 months?

If so, you could create a measure like below.

Measure =

var _axis = format(SELECTEDVALUE('Table'[yearmonth]),"yyyymm")

var _max = FORMAT(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),"YYYYMM")

var _min = FORMAT(EDATE(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),-11),"YYYYMM")

return

if(_axis>=_min&&_axis<=_max,1,0)

Then add this measure to visual filter set value = 1.

Best Regards,

Jay

[Anonymous]

Hi Jay,

Sorry to bother you but I am unable to create the formula you advice. 

The formula you advice is :

Measure =

var _axis = format(SELECTEDVALUE('Table'[yearmonth]),"yyyymm")

var _max = FORMAT(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),"YYYYMM")

var _min = FORMAT(EDATE(MAXX(ALLSELECTED('Table'),'Table'[yearmonth]),-11),"YYYYMM")

return

if(_axis>=_min&&_axis<=_max,1,0)

Then add this measure to visual filter set value = 1.

Do you have sample of this formula or anywhere I can refer?

[Anonymous]

Hi @Anonymous ,

There is one parenthesis missed after selectedvalue() function.

format(SELECTEDVALUE('Table'[yearmonth]),"yyyymm")

Best Regards,

Jay

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Thanks for the advice. I have changed my questions. 

[lbendlin]

Thank you for the sample data. Please explain what "Rolling 12 month"  mean for you. Is it the current month plus 11 month back?  Is it a sliding window around the current month? For example - which formula do you use to arrive at the 20916 value for January 2021 ?

Also - i just noticed - your primary Y axis starts at 0 but your secondary y axis starts at 3K.  In the visual settings pin the start of the secondary y axis to 0 as well.