First/Last Working Days 04-06-2018 21:32 PM - last edited 06-27-2018 11:00 AM

[Greg_Deckler]

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 Calendar2 = ADDCOLUMNS(Calendar1,"Month",MONTH([Date]))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
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 Calendar2 = ADDCOLUMNS(Calendar1,"Month",MONTH([Date]))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
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 Calendar2 = ADDCOLUMNS(Calendar1,"Month",MONTH([Date]))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
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 Calendar2 = ADDCOLUMNS(Calendar1,"Month",MONTH([Date]))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
VAR TodaysMonth = MONTH(TodaysDate)
VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = MAXX(Calendar4,[Date])
RETURN IF(TodaysDate = LastWorkingDay,TRUE(),FALSE())

eyJrIjoiZjZmMTkzYTQtYmU5Ny00OTJkLWI0YTgtZDY2ZDI5OTdlNmQ5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Anonymous]

Appreciate this is an old topic but this bit of code has really helped, thank you!

I have modified the last working day to find the last saturday for a maintenance weekend, this works really nice thanks to your code

Maintenance Check =

//this calculates the approximate date of the monthly maintenance window it looks at the weekday as 6 = Saturday then takes the max valule of the calculated calendar and subtracts 7 If the last Saturday is after the last working day, otherwise it displays the last working Saturday of the month

VAR TodaysDate = today()

VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))

VAR Calendar2 = ADDCOLUMNS(Calendar1,"Month",MONTH([Date]))

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

VAR TodaysMonth = MONTH(TodaysDate)

VAR Calendar4 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay]<6)//calculate the last working day of the month

VAR Calendar5 = FILTER(Calendar3,[Month]=TodaysMonth&&[WeekDay]=6)//calculate the last Saturday of the month

VAR LastWorkingDay = MAXX(Calendar4,[Date])

VAR LastSaturday = MAXX(Calendar5,[Date])

RETURN if(LastWorkingDay<LastSaturday,format(LastSaturday-7,"dd/mmm/yyyy"),//this takes into account months where the last saturday is the last or next to last day, minus 7 and thats the MM weekend

FORMAT(LastSaturday,"dd/mmm/yyyy"))

[JJ_JJ]

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?

Thanks in advance! 

[Greg_Deckler]

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. 

[JJ_JJ]

Great, thanks! Now I understand. 🙂

[ashaikh]

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

[Greg_Deckler]

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