Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to find a dax solution to help with some daily metrics I receive.
The issue I have is that sometimes there can be an issue with data not being received on some days.
I want to get the average weekly value.
Example of data
Name - Rows - Date
AA 24 04/01/2021
AA 28 05/01/2021
AA 21 06/01/2021
AA 27 07/01/2021
AA 23 08/01/2021
AA 24 09/01/2021
So for the week commencing mon 04/01 to Sun 10/01 there are 6 days of data total value of rows divided by 6 = 24.5
BB 24 04/01/2021
BB 28 05/01/2021
BB 21 06/01/2021
BB 24 09/01/2021
In the above example BB has data missing for 07/01 , 08/01 , 10/01 so it would be 97 divided by 4 , avg 24.25
So is there a dax measure / formula that can workout the week range to look at dates , and then count the dates for each name and get the correct average . Any help would be very much appreciated. Thanks
Solved! Go to Solution.
Hi @Pandadev ,
Create a weeknum column.
weeknum = WEEKNUM('Table'[Date])
Then refer the following formula.
Column =
var _count = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Name],'Table'[weeknum]))
var _sum = CALCULATE(SUM('Table'[Rows]),ALLEXCEPT('Table','Table'[Name],'Table'[weeknum]))
return
_sum/_count
Best Regards,
Jay
Hi @Pandadev ,
Create a weeknum column.
weeknum = WEEKNUM('Table'[Date])
Then refer the following formula.
Column =
var _count = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Name],'Table'[weeknum]))
var _sum = CALCULATE(SUM('Table'[Rows]),ALLEXCEPT('Table','Table'[Name],'Table'[weeknum]))
return
_sum/_count
Best Regards,
Jay
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
87 | |
77 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |