Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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())

 

 

Time Intelligence

6 Replies

  • ashaikh's avatar
    ashaikh
    Helper III
    8 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

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

  • JJ_JJ's avatar
    JJ_JJ
    Frequent Visitor
    8 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      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's avatar
        JJ_JJ
        Frequent Visitor
        8 years ago

        Great, thanks! Now I understand. :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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"))