Creating Calendar with Week & Month Cutoff

Power Query M Code Showcase
1

I am using Power BI Date Table Code for creating calendar in my data model.

Presently, this code ask 3 parameters . 1) StartDate 2) EndDate 3) FYStartMonth

I want to add another parameter (DayOfWeekName) for week & month cut-off.
e.g. if I select Thursday then all Thursday date will be marked as week cutoff and last Thursday of month as month cut-off.

The .pbix file is attached. Presently, I have achieved required result using groupby & merge query option.

WeekCut-off & Month Cut-off are required columns in Date table.

Looking for better approach through parameter in Power BI Date Table Code.
Week & Month Cut-off table in Calendar.pbix (34.1 KB)
Power BI Date Table code.txt (2.8 KB)

Thanks in advance

2

Bumping this post for more visibility from our experts and users.

3

Hi Prafulla,
You could start with end of week. As your end of week is Thursday the first day of the week is Friday:
Date.EndOfWeek(dateTime as any, optional firstDayOfWeek as nullable number)
With this all days of that “week” will have the Thursday’s date.
And it is easy to find the last Thursday of the month (it’s the Thursday with the highest or max date of the month).

Regards,
Matthias

1 Like