Measure average for multiple months

[SebaSpotti]

Hi all!

I have a table with two column "EvaluationDate" and "Evaluation". In the first column I have dates, in the second numbers.

Given a specific month and year that can be changed through a slicer, I want to calculate the AVERAGE(Evaluation) for that month and the previous 12 months, and show the results in an histogram or line chart.

Any suggestions?

Thank you!

[dharmendars007 · Accepted solution]

Hello @SebaSpotti , 

Firstly make sure to have "Date table" created and map date table "Dates" to "Evaluation Date" then please try the below code..

This measure will help to go back 12 month and see the data..

RollingAvgEval =
CALCULATE(AVERAGE('YourTable'[Evaluation]),
DATESINPERIOD('DateTable'[Date],
MAX('YourTable'[EvaluationDate]),-12,MONTH))

Finally, Drag the Datetable column to the axis of the chart and measure created and adjust the axis accordingly

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[SebaSpotti]

Hi!

Yes, I tried this solution but it works only for the last month (see MAX('YourTable'[EvaluationDate]) ). I would like to chose the month from which going backward. 

[SebaSpotti]

Using a second data columns it works @dharmendars007 , thanks!