cancel
Showing results for
Did you mean:

# Re: First/Last Working Days

Super User
2829 Views
Super User

## First/Last Working Days

Builds off of First Working Day of Week here: https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Working-Day-of-Week/m-p/391332

Provides the same functionality for First/Last Working Day of the Week, Month, Quarter and Year as well as logical true/false tests for these. Included below are the ones for Month, everything is a variation on this theme.

First Working Day of Month

```First Working Day of Month =
VAR TodaysDate = TODAY()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = MONTH(TodaysDate)
VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay1]<6)
VAR FirstWorkingDay = MINX(Calendar4,[Date])
RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy")```

Last Working Day of Month

```Last Working Day of Month =
VAR TodaysDate = TODAY()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = MONTH(TodaysDate)
VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = MAXX(Calendar4,[Date])
RETURN FORMAT(LastWorkingDay,"mm/dd/yyyy")```

Is First Working Day of Month

```Is First Working Day of Month =
VAR TodaysDate = TODAY()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = MONTH(TodaysDate)
VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay1]<6)
VAR FirstWorkingDay = MINX(Calendar4,[Date])
RETURN IF(TodaysDate = FirstWorkingDay,TRUE(),FALSE())```

Is Last Working Day of Month

```Is Last Working Day of Month =
VAR TodaysDate = TODAY()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = MONTH(TodaysDate)
VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = MAXX(Calendar4,[Date])
RETURN IF(TodaysDate = LastWorkingDay,TRUE(),FALSE())```

Proud to be a Datanaut!

Member

## Re: First/Last Working Days

I am not concerned with the Working Day. I jsut need the start date and end date from Sunday to Saturday.

Super User

## Re: First/Last Working Days

You just need to adjust the filter then so that it includes all days instead of excluding 6 and 7. Calendar4 temp table.

Proud to be a Datanaut!

Frequent Visitor

## Re: First/Last Working Days

Hi!

I'm trying to modify this code so that I get the first day of the current quarter instead of week. But as I'm new with Power BI, could you please explain a couple of things for me?

1. Where does [Date] come from in this line:

`VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))`

2. I don't understand the purpose of these Calendar variables. They are variables, but then Calendar1 is a table. But Calendar2 is a different colunn in the same table? Is Calendar2 the column name and if not, what is the difference between Calendar2 and the column name usage?

Highlighted
Super User

## Re: First/Last Working Days

The PBIX includes first/last day of quarter as displayed in the Publish to Web report.

That being said, I broke that out for clarity. Each VAR is actually a table. So, the first Calendar VAR returns a table of dates. The second calendar VAR adds a column to that table as does the 3rd calendar. But all are tables.

Date comes from the Calendar table. When you generate a Calendar table using the calendar functions you get back a table with a single column, [Date]. Just how it works. You can see that if you take the line that generates the calendar table and create a New Table in Power BI Desktop and paste in that code.