How to calculate the number of Sundays in the month based on a date in another column

[brookesmoore]

Hello,

I am looking to add a column to a table that will calculate the number of Sundays in a month based on a date from another column.

I have a column called "Start of Week" that has a date of a Sunday denoting the beginning of a week. I would like to calculate the number of Sundays in the month of the date in the Start of Week column. For example, my first entry is 2/14/2021, so I would like the "Number of Sundays" column to show '4'. 

Any help is greatly appreciated. Thank you!

[Anonymous · Accepted solution]

Hi  @brookesmoore ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table1=
CALENDAR(
    DATE(2021,1,1),
    DATE(2023,12,31))
return
ADDCOLUMNS(
    _table1,"weekday",WEEKDAY([Date],2))

2. Create calculated column.

Column =
var _day=
WEEKDAY('Table'[Start of Week],2)
return
CALCULATE(
   COUNT('Table 2'[weekday]),
    FILTER(ALL('Table 2'),
    YEAR('Table 2'[Date])=YEAR(EARLIER('Table'[Start of Week]))&&MONTH('Table 2'[Date])=MONTH(EARLIER('Table'[Start of Week]))&&'Table 2'[weekday]=_day),'Table 2'[weekday])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[brookesmoore]

This worked as needed. Thank you for your help!

[brookesmoore]

I simply have a column of Transaction Dates, and have created a "Start of Week" column based off those dates. The Start of Week is always a Sunday. I would like to add another column that will tell me how many Sundays are in the month that is being shown in the "Start of Week" column.

So if the Transaction Date is 2/17/2021, the Start of Week will be 2/14/2021, and the Number of Sundays will be 4

[ryan_mayu]

could you pls provide some sample data?