cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Calculating number of days between two dates_1.jpg

 

  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 ()
            )
    )
    Calculating number of days between two dates_2.jpg
  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]
            )
        )
    )
    Calculating number of days between two dates_3.jpg

View solution in original post

4 REPLIES 4
mircea Frequent Visitor
Frequent Visitor

Re: Calculating number of days between two dates

Please have alook at this, it may answer your question:

 

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

Re: Calculating number of days between two dates

Please have alook at this, it may answer your question:

 

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

 

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

Calculating number of days between two dates_1.jpg

 

  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 ()
            )
    )
    Calculating number of days between two dates_2.jpg
  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]
            )
        )
    )
    Calculating number of days between two dates_3.jpg

View solution in original post

PaulMac Member
Member

Re: Calculating number of days between two dates

@v-haibl-msft 

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

PowerBI-ERROR.PNGWhy doesn't it like these columns? Just because they are not DAX!!??PowerBI-ERROR2.PNGFrustrating 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?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,226)