Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How Many Certain Weekdays are in a Month

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?

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

3 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    4 years ago

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

     

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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