cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506
Helper IV
Helper IV

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
v-haibl-msft
Microsoft
Microsoft

@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
v-haibl-msft
Microsoft
Microsoft

@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

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

Why doesn't it like these columns? Just because they are not DAX!!??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 BIFrustrating 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?

MR2001
Helper II
Helper II

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

 

mircea
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors