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

  • sureshdam18's avatar
    sureshdam18
    Frequent Visitor
    8 years ago

    Hi,

     

    I want to calculate Network days between Created Date to Till Date, My date filed looks like,

     

    How can we Calculate, If i am tring the above Dax calculation it is geving the error. calculation is,

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

    NetworkDays = CALCULATE(SUM([is work day]),
               DATESBETWEEN([Created Date].[Date],Sheet1[Created Date],TODAY()
              ))

    Open Request = IF(AND('Fusion dashboard report'[Completion Status]="Open",  [Status]="MDM Ops Review - Undelete"),'Fusion dashboard report'[Network days], -1)

     

     While i am using this Calculation it showing the below error.

    A circular dependency was detected: Fusion dashboard report[Network days], Fusion dashboard report[Open - FLS / Requestor in Days], Fusion dashboard report[Network days].

    please help me on this.

     

     

    Thanks in Advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    This looks fine, how to get number of hours as well, so times tickets open on same day and closed on same day. the mentioned formula get 1 day for this as well. but wanted to claculate hours in this scenario.

     

    Thanks,
    Thimma

  • JoMann's avatar
    JoMann
    Regular Visitor
    7 years ago

    Hi,

    When I try to create the measure (last step of your instructions) i get an error message: "A single value for column 'Total Revenue' in table 'SalesData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

    Ho wdo i resolve that? Also are we meant to create a relationship with the Date/Calendar Table and the table containing our start and end dates?

  • nirvana_moksh's avatar
    nirvana_moksh
    Impactful Individual
    7 years ago

    Anonymous - hello, I tried this but it errored about because there are instances of nulls in my oldest date and newest date. Is there a way to handle that?

    • jcasallo's avatar
      jcasallo
      New Member
      7 years ago
      Replace text in red by your date fields.
       
      Business_Days = COUNTROWS(FILTER(CALENDAR(DATE(2019,1,1),date(2019,12,31)), WEEKDAY([Date])<>1 && WEEKDAY([Date])<>7))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I want Values Row by row in Calculated column followed same way as you mentioned below and it is not giving me negtive values,Please find the Below Data whichi have and Screen Captures.

    Thanks In Advance.

     

    Dax Formula:

    TBL_Date=DATESBETWEEN('Calender Table'[Date].[Date],MasterData[Invoice Date],MasterData[Payment Date])
    var TBL_FinalDate=
    ADDCOLUMNS(TBL_Date,"Working day",IF(WEEKDAY([Date],2)>=6,0,1),
    "Holiday",IFERROR(LOOKUPVALUE('Holiday-2019'[Holidaycount],'Holiday-2019'[Holidays - 2019,2020],[Date]),0)
    )
    return
    SUMX(TBL_FinalDate,if([Working day]=1&&[Holiday]=0,1,0))