cancel
Showing results for 
Search instead for 
Did you mean: 

First/Last Working Days

Super User
3830 Views
Super User
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 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())

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ashaikh Member
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
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


JJ_JJ Frequent Visitor
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?

 

Thanks in advance! 

Super User
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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


JJ_JJ Frequent Visitor
Frequent Visitor

Re: First/Last Working Days

Great, thanks! Now I understand. Smiley Happy