Calculate Average Measure for Current, Previous and Month Before

[Daniel_B]

Hi Everyone

I've tried to create a measure which calculates an average score for the current month (based on the latest data), previous month and the month before but the average is not correct as I manually checked it against the source data

My data is dummy data for testing which has;

2 years of data (2018 and 2019) 

240 rows (10 for each month)

Each month shows the date format as dd/mm/yyyy (example 01/01/2018, 01/02/2018 etc) and is formatted in Power BI as "Date"

The measure I tried is below

Current Recommend Score = (CALCULATE

(AVERAGE(Data[RecommendScore]),FILTER(Data,MONTH(Data[Date])=MONTH(TODAY()))))

As my current month didn't work, there's no reason why any attempts at the previous 2 months would yield better results

Ideally, I would also like to include a conditional format to compare the months to one another showing an up, down or neutral arrow if they are up, down or the same respectively.. I have something different on another report where if latest month > previous month then 1 else 0 (see below) and I've tried to replicate it but again to no avail

Met/Not Met = IF([MTD Postive]>[LM Positive],1,0)

Any ideas on what I'm doing wrong?

Thanks

Dan_B

[V-lianl-msft · Accepted solution]

Hi @Daniel_B ,

If your dates are not consecutive, you can create a calendar table.Then create a measure to calculate the average value of the last three months, and use the newly created calendar as the slicer.You can refer to the pbix.

Best Regards,

Liang

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

[Daniel_B]

If possible, I'd like to learn how to make it so that if there is a calendar page filter that the user can choose the year and month and the data still show a comparison of the selected month in the selected year

Otherwise, just being able to see the latest 3 comparable months would be great