Forum Discussion
Helper IFOREACH / WHERE Statement in DAX
Hi All,
Feels like this should be straight-forward but I'm struggling with how to apply a calculation to sub-groups in my table without a WHERE statement.
I'm trying to identify how many weeks there are in each calendar month but the closest I've been able to get is the max number of weeks that can occur in any calendar month.
What I'm looking for is something resembling the following:
FOREACH [YearMonth] MAX(Weeks in Month)
Basic Table Format:
Master Date Year Fisc.Yr Quarter Fisc. Qtr Month Week Day YearMonth
| 01/01/2022 | 2022 | 2022 | 1 | 3 | 1 | 1 | 1 | 2022_1 |
| 02/01/2022 | 2022 | 2022 | 1 | 3 | 1 | 2 | 2 | 2022_1 |
| 03/01/2022 | 2022 | 2022 | 1 | 3 | 1 | 2 | 3 | 2022_1 |
...
Any advice would be greatly appreciated.
Hi,
In DAX it could be straightforward :
If you make a table with YearMonth (in lines) and Week number
with a COUNT or DISTINCTCOUNT as calculation, you'll get it.Or a DAX measure could be something like :
# of Week = CALCULATE( DISTINCTCOUNT( MonCalendrier[NumSemISO] ) , ALLEXCEPT( MonCalendrier , MonCalendrier[Annee-Mois] ) )Tell us if it works and mark it as solved or tell us more about your issue.
Yes, not sure how I missed it - suspect I was overcomplicating things 🙂 - but the below seems to have worked:
Weeks in Month = CALCULATE(DISTINCTCOUNT([Week of Month]), ALLEXCEPT('DateMaster', DateMaster[Fiscal YearMonth]))
2 Replies
- AilleryO
Memorable Member
Hi,
In DAX it could be straightforward :
If you make a table with YearMonth (in lines) and Week number
with a COUNT or DISTINCTCOUNT as calculation, you'll get it.Or a DAX measure could be something like :
# of Week = CALCULATE( DISTINCTCOUNT( MonCalendrier[NumSemISO] ) , ALLEXCEPT( MonCalendrier , MonCalendrier[Annee-Mois] ) )Tell us if it works and mark it as solved or tell us more about your issue.
PaulBodenYes, not sure how I missed it - suspect I was overcomplicating things 🙂 - but the below seems to have worked:
Weeks in Month = CALCULATE(DISTINCTCOUNT([Week of Month]), ALLEXCEPT('DateMaster', DateMaster[Fiscal YearMonth]))