Forum Discussion
RemoveFilters is not working
Anonymous
I may suggest_Inc SC Avg Count by W# = DIVIDE ( AVERAGEX ( VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ), CALCULATE ( [_Inc Count], ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name] ) ) ), CALCULATE ( DATEDIFF ( MIN ( INCIDENTS[Date] ), MAX ( INCIDENTS[Date] ), MONTH ), ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name)] ) ) )Tamerj1 is so beyond awesome, it's amazing. Never gave up...stayed with it for DAYS and HOORAY!!!!! FOUND THE SOLUTION.
THANK YOU tamerj1 !!!!!!!!
Ok, we are soooooo close. I changed the Count of Months formula to yours with a minor adjustment (the min date is a hard-coded date of Dec 1, 2021 (the actual date table goes back further) and added 1. The only thing about the formula is that it returns the same denominator for each week. This isn't always the case. For example, right now, W1, W2 and W3 would be divided by 6 but W4 would be divided by 5 since we don't have current data for that yet (there are only 5 data points for W4). Did that make any sense?
Community ChampionAnonymous
I may suggest
_Inc SC Avg Count by W# =
DIVIDE (
AVERAGEX (
VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
CALCULATE (
[_Inc Count],
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name] )
)
),
CALCULATE (
DATEDIFF ( MIN ( INCIDENTS[Date] ), MAX ( INCIDENTS[Date] ), MONTH ),
ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (Week Name)] )
)
)
Tamerj1 is so beyond awesome, it's amazing. Never gave up...stayed with it for DAYS and HOORAY!!!!! FOUND THE SOLUTION.
THANK YOU tamerj1 !!!!!!!!