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

The issue is the Month formula requires an integer value it can't convert the text based month i.e. January.  You can try a new column that converts the months to a value something like IF(MyMonth - "Januaury", 1, IF(MyMonth = "Februaury", 2 ...  etc.

juliarusmanica's avatar
juliarusmanica
New Member
5 years ago

Do I add that column to my table [ReceivingLog]? or do I click Add data and create a table with one column [MonthNumber}?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes, but I just looked at your formula again and see you are taking year, month, and day from a date field.  If ReceivingLog[Receipt Date] is formated then you don't need to put it in a DATE formula i.e. COUNTAX(FILTER(ReceivingLog,ReceivingLog[Receipt Date]=>...