Count Number of Fridays in a Month

[arock-well]

I would like to add a column that calculates and displays the number of Fridays in a given month. For example, if my Date column says '1/4/2023' the resulting number should be 4, as January 2023 has 4 Fridays in the month.

If my Date column says '3/8/2023' the resulting number should be 5, as March 2023 has 5 Fridays in the month.

[Idrissshatila]

Hello, 

You could use this measure to calculate the number of fridays in this current month .

NumOfFridays Correct =

CALCULATE (

    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[Day of Week Number] = 5 ),

    DATESMTD ( 'Date'[Date] )

)

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos👍

Follow me on Linkedin

[arock-well]

@Idrissshatila Could you expand on what each of those steps are trying to do? For some reason I get a huge total number on each of the dates in the table no matter the date. 

For example, 9/2/2022 shows a whopping 60245, but there are 4 Fridays in that month.

December 2022 should have 5 Fridays in the month.

It's obvious I'm not understanding how this should be working.

[Idrissshatila]

Hello,

First you should have a calculated column in the date table that shows the day number of the week. Then what we do in the measure is we calculate the count of the rows were day number of the week is 5 which is friday. Then we calculate around this calculation to filter it so it shows only fridays of this current month which is MTD ( Month to date).

If I answered your question, please mark my post as solution so it would appear to others, Appreciate your Kudos👍

Follow me on Linkedin

[arock-well · Accepted solution]

Thanks @Idrissshatila . However, I think this post gets it to where I need it: Solved: How to get number of weeks in a month - Microsoft Power BI Community

Specifically this: 

Weeks = var __SoM = EOMONTH([Date],-1)+1 return CEILING((EOMONTH([Date],0)-__SoM+WEEKDAY(__SoM,16)-6)/7,1)

[grandtotal]

You can easily create a custom date table for this, check out this one:

let
Today = Date.From( DateTime.LocalNow() ),
StartDate = #date(2022, 1, 1),
EndDate = #date(2022, 12, 31),
#"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
#"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
#"Insert Month Number" = Table.AddColumn(#"Converted to Table", "Month Of Year", each Date.Month([Date]), Int64.Type),
#"Insert Day Name" = Table.AddColumn(#"Insert Month Number", "Day Name", each Date.DayOfWeekName([Date], "EN-us" ), type text),
#"Insert Day Name Short" = Table.AddColumn( #"Insert Day Name", "Day Name Short", each Date.ToText( [Date], "ddd", "EN-us" ), type text),
#"Insert Day of Week" = Table.AddColumn(#"Insert Day Name Short", "Day of Week Number", each Date.DayOfWeek([Date]), Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Insert Day of Week", "IsFriday", each if [Day of Week Number] = 4 then 1 else 0, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Month Of Year"}, {{"FridayCount", each List.Sum([IsFriday]), type number}, {"AllRows", each _, type table [Date=date, Month Of Year=number, Day Name=text, Day Name Short=text, Day of Week Number=number, IsFriday=number]}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date", "Day Name", "Day Name Short", "Day of Week Number"}, {"Date", "Day Name", "Day Name Short", "Day of Week Number"})
in
#"Expanded AllRows"

The key steps are from #"Added Conditional Column"

[arock-well]

Thanks @Idrissshatila . However, I've gone the route I mentioned in the previous reply, although that may be very useful for others later on.

Thanks for your quick replies!