FOREACH / WHERE Statement in DAX

[PaulBoden]

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.

[AilleryO · Accepted solution]

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.

[AilleryO · Accepted solution]

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.

[PaulBoden · Accepted solution]

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]))