Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I am trying to find a weighted average where the average changes based on a date heirarchy filter; The filter is an individual date and start of week.
For example, if I am viewing a bar chart that is at the individual date level, the weighted average for each date should be row 'Count'/Total count for that specific date * Number. If my filter is the "start of week", in this case 1/3/2022, I want the weighted average to be row "Count"/Total count for that specific week (in this case 1/3-1/9) *Number. Basically, I want the denominator to change based on whether I am viewing it at the daily level or the weekly level. Thank you so much in advance!
Solved! Go to Solution.
@DEFresh3451 , Create a date table with week columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Avg of Week =CALCULATE(Averagex(Values(Date[Date]),CALCULATE(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
@DEFresh3451 , Create a date table with week columns
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Avg of Week =CALCULATE(Averagex(Values(Date[Date]),CALCULATE(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |