cancel
Showing results for
Did you mean:
aktripathi2506 Member

## Calculating number of days between two dates

Hi,

I have two dates columns and I want to calculate the number of working days between those two dates? like network days in excel.

How can we do this here in power BI?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions v-haibl-msft
Microsoft

## Re: Calculating number of days between two dates

@aktripathi2506

Assume you have a dataset as below. We can create a calendar table and add a column in it to mark the working days. Then create a column in NetWorkDays table to show the number of working days. 1. Create a calendar table and add a column. Create a 1:1 relationship between DimDate(Date) and Holidays(Date).
```DimDate =
CALENDAR ( MIN ( NetWorkDays[Start date] ), MAX ( NetWorkDays[End date] ) )

WorkingDay_Mark =
VAR WeekDayNum =
WEEKDAY ( DimDate[Date] )
RETURN
(
IF (
OR (
OR ( WeekDayNum = 1, WeekDayNum = 7 ),
RELATED ( Holidays[Date] ) <> BLANK ()
),
FALSE (),
TRUE ()
)
)``` 2. Create a column in NetWorkDays table.
```WorkingDay_Num =
COUNTROWS (
FILTER (
DimDate,
AND (
AND (
DimDate[Date] >= NetWorkDays[Start date],
DimDate[Date] <= NetWorkDays[End date]
),
DimDate[WorkingDay_Mark]
)
)
)``` 4 REPLIES 4
mircea Frequent Visitor

## Re: Calculating number of days between two dates

http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/

Can you try something like:

```=CALCULATE(SUM(Dates[IsWorkday]),
DATESBETWEEN(Dates[Date],
TwoDates[Column1],
TwoDates[Column2]                       )
)```
MR2001 Regular Visitor

## Re: Calculating number of days between two dates

http://www.powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/

Can you try something like:

```=CALCULATE(SUM(Dates[IsWorkday]),
DATESBETWEEN(Dates[Date],
TwoDates[Column1],
TwoDates[Column2]                       )
)``` v-haibl-msft
Microsoft

## Re: Calculating number of days between two dates

@aktripathi2506

Assume you have a dataset as below. We can create a calendar table and add a column in it to mark the working days. Then create a column in NetWorkDays table to show the number of working days. 1. Create a calendar table and add a column. Create a 1:1 relationship between DimDate(Date) and Holidays(Date).
```DimDate =
CALENDAR ( MIN ( NetWorkDays[Start date] ), MAX ( NetWorkDays[End date] ) )

WorkingDay_Mark =
VAR WeekDayNum =
WEEKDAY ( DimDate[Date] )
RETURN
(
IF (
OR (
OR ( WeekDayNum = 1, WeekDayNum = 7 ),
RELATED ( Holidays[Date] ) <> BLANK ()
),
FALSE (),
TRUE ()
)
)``` 2. Create a column in NetWorkDays table.
```WorkingDay_Num =
COUNTROWS (
FILTER (
DimDate,
AND (
AND (
DimDate[Date] >= NetWorkDays[Start date],
DimDate[Date] <= NetWorkDays[End date]
),
DimDate[WorkingDay_Mark]
)
)
)``` PaulMac Member

## Re: Calculating number of days between two dates

When creating a new column using the below code, this method equates to an #ERROR (see attached screenshot)

```WorkingDay_Num =
COUNTROWS (
FILTER (
DimDate,
AND (
AND (
DimDate[Date] >= NetWorkDays[Start date],
DimDate[Date] <= NetWorkDays[End date]
),
DimDate[WorkingDay_Mark]
)
)
)``` Why doesn't it like these columns? Just because they are not DAX!!?? Frustrating that forum user examples vary from what can actually be done in Power BI

Were your columns on the 'NetworkDays' table named [Start Date] & [End Date] DAX/Measures?

Is there any reason why I can only choose measures?

Announcements   