Measure to calculate Average of Numbers from Specific Dates

[pilotguy251]

I am relatively new to Power BI and have been experimenting with a dashboard that I inherited at work. I am trying to create a measure that will calculate the average of numbers that occur on specific dates. For example, from the image below, I would like the measure to calculate the average of the numbers that occur on 1/1/2021, 1/1/2022/ and 1/1/2023, so on and so forth, and do the same thing for the other months as well. For the Januarys, this average would be 16,021. Feburarys, 14,903, etc. Is this known as a rolling average? I have looked at some posts regarding that, but was not successful in getting the measure to work as I needed it to. Any help would be greatly appreciated.

[selimovd · Accepted solution]

Hey @pilotguy251 ,

you can use CALCULATE to change the filter context. For example to consider only the values from Janury 1st of every year:

Avg January 1st =
CALCULATE (
    AVERAGE ( myTable[Number] ),
    MONTH ( myTable[Date] ) = 1
        && DAY ( myTable[Date] ) = 1
)

If you want to make it a rolling average you can add the DATESINPERIOD function to CALCULATE.

But for that you need a proper DATE table in order for the time intelligence functions to work.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[pilotguy251]

@selimovd Thank you so much for your reply! I will try this out first thing Monday morning when I get back to the office and will be back in touch. There is a date table included in the set, so I will try both methods. 

[selimovd]

Sure, just let me know if it works or if you need more help.

Best regards and good luck

Denis

[pilotguy251]

The CALCULATE function seems to have worked. I did find that the dataset did not contain a proper date table to use the DATESINPERIOD Function. I am in the process of trying to build one as I think I will finally have it solved after this.