Forum Discussion

kyleldi's avatar
kyleldi
Frequent Visitor
9 years ago
Solved

NETWORKDAYS type function in PowerBI

I have a table (vw_WorkCenterUtilization) and a date column (Work_Date) that I need to calculate the oldest and newest date in the data, and the number of work days within it.  I'm using the FIRSTDAT...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi kyleldi,


    Firstly, create a calendar table in Power BI Desktop following the guide in this blog. Then create a column in the calendar table using the Dax below.

    is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

     

    Secondly, create a measure using the following DAX and create a card visual.

     

    Days= CALCULATE(SUM('Date'[is work day]),
           DATESBETWEEN('Date'[Date], 
                        [OldestDate],
                      [NewestDate]
          ))




     

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I found an easier solution one column and one measure, but it could be a column if you don't need it to be dynamic.

 

Column

Weekday = WEEKDAY(WEEKDAY(MyTable[MyDate],2)) Note: the 2 at the end makes it Monday 1 - Sunday 7

 

Measure

NetWorkDays = COUNTAX(FILTER(MyTable,AND(MyTable[MyDate]>= MyTable[MyStartDate], MyTable[Weekday]<6)), MyTable[MyDate]) - COUNTAX(FILTER(MyTable,AND(MyTable[MyDate]> MyTable[MyEndDate], MyTable[Weekday]<6)), MyTable[MyDate]) Note: this is counting all weekdays after the start date and then subtracting all weekdays after the end date.

 

If you needed to deal with holidays it would be pretty simple as well, just add a new column to indicate Holidays or even a seperate holiday date table and you can use that to filter out the holidays from your count.