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

QooT's avatar
QooT
Frequent Visitor
4 years ago

Maybe its old topic but easises solution is to create a custom function lik this:
(StartDate as date, EndDate as date) as number =>
let
DateList = List.Dates(StartDate, Number.FromText(EndDate-StartDate), #duration(1,0,0,0)),
RemoveWeekdns = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday)<5),
CountDays = List.Count(RemoveWeekdns)
in
CountDays

And then just invoke it whenever needed.

  • Stephen_Sink's avatar
    Stephen_Sink
    Frequent Visitor
    4 years ago

    Do I have to update my version of desktop to get this? im running 2.105

  • newpowerbiuser8's avatar
    newpowerbiuser8
    Icon for Helper I rankHelper I
    3 years ago

    Hi,

     

    I have this formula 

    Days with BSCM = IF('Sheet 1'[Send form to BSCM]="No","N/A",IF(ISBLANK('Sheet 1'[BOR updated date]),"",FORMAT(NETWORKDAYS('Sheet 1'[Form Submitted on],IF(ISBLANK('Sheet 1'[BOR updated date]),TODAY(),'Sheet 1'[BOR updated date]),1),"#")))
     
    Where it shows blank, I need to calculate days between Form Submitted on and Today
     
    Your help will be really appreciated.