Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
@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.
Sure, just let me know if it works or if you need more help.
Best regards and good luck
Denis
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.
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |