How Many Certain Weekdays are in a Month

[Anonymous]

Probably has been discussed, but looking for some assistance as i have not found anything online.  Is there a way to add a custom column to a specific date (ex. 9/1/2021) and it returns how many "Mondays" are in that month?

[Anonymous]

@Jakinta pretty slick!

[mahoneypat · Accepted solution]

Here is a DAX column expression that shows one way to do it.  Replace T1 with your actual table name.  Note that you should make your column type Date instead of type DateTime.

Mondays =
VAR thisdate = T1[Week]
VAR thisEOM =
    EOMONTH ( thisdate, 0 )
VAR thisCalendar =
    CALENDAR ( thisdate, thisEOM )
VAR result =
    COUNTROWS ( FILTER ( thisCalendar, WEEKDAY ( [Date] ) = 2 ) )
RETURN
    result

Pat

[Jakinta]

You can add this step to ypur query.

I have used Date.StartOfMonth as caution in case the date in Week column is not 1st day of month.

= Table.AddColumn( PriorStepName , "MondaysCount", each let s=Number.From(Date.StartOfMonth(Date.From([Week]))), e=Number.From(Date.EndOfMonth(Date.From([Week]))), Dates=List.Transform({s..e}, Date.From) in List.Count( List.Select( Dates, each Date.DayOfWeek(_)=1 ) ) )