Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate working days between 2 dates

Hi All,

 

I was trying for long time and couldn’t break this through. I was trying to get the “networkdays” in excel equivalent at PowerBI.

After rounds of searching online, I believe the below should able to fix my requirement.

 

Firstly, create a calendar table in Power BI Desktop, 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]))

          

However, I’m stuck with “Datesbetween” formula – I’m quite puzzled what is ‘Date’[Date]. Assuming I have a start date and an end date, but what’s ‘Date’[Date]?

 

I have also created a calendar in separate table, and managed the relationship. However, still getting the errorr.

 

Appreciate if you could help me out with this query. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Found the solution, after looking at all the threads. simply mistake = don't use New Measure, use New Column

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    Datesbetween is an inbuilt time intelligence function.  These inbuilt functions are normally "syntax sugar" - in other words there is a more complex formula under the hood, but you are protected from it. All inbuilt time intelligence functions need to know where the date column is in your calendar table to be able to do the job. That is what this parameter is for - to tell the function where the date column is. You will see it in all inbuilt time intelligence functions

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      thanks...i got this error.

       

      A single value for column 'Created Date Time' in table 'KPI' 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Apologoies. Allowed me to elaborate. I have a column (consist of 30k+) of transaction created date, resolved date. I want to calculate the working days between these 2 dates. May I know what is the best way to calculate working days? (I don't need to involve Public Holidays).

         

        My current formula as such

        CALCULATE(SUM(Calendar_Updated[Work/NonWowrk Day]),DATESBETWEEN(Calendar_Updated[Date],('KPI'[Created Date Time],'KPI'[stat_DateTime Resolved]))