Forum Discussion
How many Mondays in month
Hi All,
I've searched the forum and found some solutions to this but the closest that works is here:
Old thread so I didn't want to re-open.
I've pinched Jakinta's solution and reworked it:
= Table.AddColumn(#"Added Custom5", "MondaysCount", each let s=Number.From(Date.StartOfMonth(Date.From([Start of Week]))),
e=Number.From(Date.EndOfMonth(Date.From([Start of Week]))), Dates=List.Transform({s..e}, Date.From)
in List.Count( List.Select( Dates, each Date.DayOfWeek(_)=1 ) ))
So it's working for all months apart from July/August. Sample data is below - any idea why it thinks there are 5 mondays in August?
Thanks
| Start of Week | DistinctCountSource_Name | MonthYear | StartOfMonth | MondaysCount |
| 12/06/2023 | 1 | 2023-06 | 01-Jun-23 | 4 |
| 19/06/2023 | 1 | 2023-06 | 01-Jun-23 | 4 |
| 26/06/2023 | 1 | 2023-06 | 01-Jun-23 | 4 |
| 21/08/2023 | 1 | 2023-08 | 01-Aug-23 | 5 |
| 28/08/2023 | 1 | 2023-08 | 01-Aug-23 | 5 |
| 04/09/2023 | 1 | 2023-09 | 01-Sep-23 | 4 |
| 02/10/2023 | 1 | 2023-10 | 01-Oct-23 | 5 |
| 31/07/2023 | 1 | 2023-07 | 01-Jul-23 | 4 |
| 01/05/2023 | 1 | 2023-05 | 01-May-23 | 5 |
| 18/09/2023 | 1 | 2023-09 | 01-Sep-23 | 4 |
| 17/07/2023 | 1 | 2023-07 | 01-Jul-23 | 4 |
| 17/04/2023 | 1 | 2023-04 | 01-Apr-23 | 4 |
Hi P0ach3r1 ,
For me it worked with a 0. The reason is probably what HotChilli said 🙂
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
3 Replies
- HotChilli
Community Champion
The dayofWeek test (Date.DayOfWeek(_)=1 )is looking for 1 so there's probably a cultural problem for which day is the start of the week. Why don't you replace that with a test for Monday explicitly?
Date.DayOfWeekName(_) = "Monday" - tackytechtom
Most Valuable Professional
Hi P0ach3r1 ,
For me it worked with a 0. The reason is probably what HotChilli said 🙂
Hope this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ Hi Both,
Thanks for replying. looks like 0 does the trick (Monday returned blanks) - but problem now fixed.
Thanks
