Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Given a year (E.g. 2021) and month (E.g. October) how do you find out the date of the first Sunday of October, 2021?
I had a look look a the StartOfWeek function in M but it may take me to a previous month if the day of the week starts prior to the first day of the month.
Date | First Sunday of the Month |
1 Oct 2021 | 4 Oct 2021 |
So in my example, regardless whether the date is 1 Oct 2021 or 31 Oct 2021 it should return 4 Oct 2021 as the the date of the first Sunday in the month of October.
Solved! Go to Solution.
You can still use the M code StartOfWeek function; but start on the 7th of the month instead of the 1st:
#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
Date.AddDays(
Date.StartOfMonth([Date]),6)), type date)
If you want the first Monday (or other day) of the month, you can use the optional argument for start of week:
#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
Date.AddDays(
Date.StartOfMonth([Date]),6), Day.Monday))
You can still use the M code StartOfWeek function; but start on the 7th of the month instead of the 1st:
#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
Date.AddDays(
Date.StartOfMonth([Date]),6)), type date)
Why are you posting duplicates of my responses? Not only in this thread, but in others also.
You can still use the M code StartOfWeek function; but start on the 7th of the month instead of the 1st:
#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
Date.AddDays(
Date.StartOfMonth([Date]),6)), type date)
If you want the first Monday (or other day) of the month, you can use the optional argument for start of week:
#"Added Custom" = Table.AddColumn(typed, "First Sunday of Month", each Date.StartOfWeek(
Date.AddDays(
Date.StartOfMonth([Date]),6), Day.Monday))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.