Rolling 12 months average

[Anonymous]

Hello,

I know there are a number of questions on Rolling 12 months already, However, i was not able to find one that fits my needs.

I need to create a bar chart with months on x-axis and values on y-axis as mentioned below:

Show last 12 months until previous month (Since we are in Feb now, the chart should show Jan-21 to Feb-20 on x-axis)

Calculation:

Each bar in the chart should display value as Jan-21 = (Jan-21 + Dec-20 +…. + Mar-20 + Feb-20)/12

Similarly for Dec-20 = (Dec-20 + Nov-20 +…. + Feb-20 + Jan-20)/12

Nov-20 = (Nov-20 + Oct-20 +…. + Jan-20 + Dec-19)/12

.

.

.

Feb-20 = (Feb-20 + Jan-20 +…. + Apr-19 + Mar-19)/12

Source Data:

Month-Year	Value
Jan-19	50
Feb-19	30
Mar-19	10
Apr-19	60
May-19	30
Jun-19	20
Jul-19	10
Aug-19	10
Sep-19	40
Oct-19	70
Nov-19	40
Dec-19	50
Jan-20	60
Feb-20	30
Mar-20	40
Apr-20	60
May-20	20
Jun-20	30
Jul-20	70
Aug-20	10
Sep-20	30
Oct-20	50
Nov-20	20
Dec-20	30
Jan-21	50
Feb-21	20

Required (in a bar chart):

	Sum	Sum/12
Feb-20	430	35.83333
Mar-20	460	38.33333
Apr-20	460	38.33333
May-20	450	37.5
Jun-20	460	38.33333
Jul-20	520	43.33333
Aug-20	520	43.33333
Sep-20	510	42.5
Oct-20	490	40.83333
Nov-20	470	39.16667
Dec-20	450	37.5
Jan-21	440	36.66667

Thank you in advance.

[Ashish_Mathur]

Hi,

When you say "Show last 12 months until previous month", then for the December 2020 column of the chart shouldn't the range for calculation be 1 December 2019 to 30 November 2020?  Why have you have mentioned the range as 1 January 2020 to 31 December 2020?

See if my Blog here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

Hi Ashish,

For the calculation of sum, it has to consider 12 months backwards including the respective month i.e.,

for Jan 2021 = Jan 21 + Dec 20 + Nov 20 + ........ + Feb 20

However, in the chart, i need only the last 12 months starting from previous month on the x-axis i.e., since we are in Feb 21 now, the chart should show data for Jan 21, Dec 20, Nov 20..... Feb 20

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Sum =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))

var _2=
CALCULATE(
    SUM('Table'[Value]),
    FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year])))
    return _2

Sum/12 =
var _1=
IF(MAX([month])<>12,
CALCULATE(MAX('Table'[Month-Year]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])-1&&'Table'[month]=MAX('Table'[month])+1)),
DATE(YEAR(MAX([Month-Year])),1,1))

var _2=
CALCULATE(
    SUM('Table'[Value]),
    FILTER(ALL('Table'),[Month-Year]>=_1&&[Month-Year]<=MAX([Month-Year]))) 
    return _2/12

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the solution, this works just right.

[amitchandak]

@Anonymous , Try measure like this with date table

divide( CALCULATE([sum],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), not(isblank([sum]))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi Amit,

Thank you for the response.

I tried your measure, but i get this error in DAX

Could you please explain what is the denominator part of your formula trying to calculate?

i.e., CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), not(isblank([sum]))) 

[amitchandak]

@Anonymous , Try like

divide( CALCULATE([sum],DATESINPERIOD('DateTable'[Date ],MAX('DateTable'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('DateTable'[Month Year]),DATESINPERIOD('DateTable'[Date],MAX('DateTable'[Date]),-12,MONTH), filter('Table',not(isblank([sum])))))