Calculate working days between 2 dates

[Anonymous]

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!

[MattAllington]

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]

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]

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

[Anonymous · Accepted solution]

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