Get weekly average of totals even when some daily data is missing

[Pandadev]

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

[v-jayw-msft · Accepted 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