• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Difference between 2 dates(excluding weekends) in Power BI

Hi All,

There are 2 Date Columns in my Table. I need to add a third column in this table in Power BI Desktop which will calculate the Number of Days (Not in whole number but in Decimal Data Type) between two Dates(Data Type is Date/Time) excluding Weekends (Saturdays and Sundays).

Example - If Start Date is 28th Jan, 2022 11:20:15 AM and End Date is 2nd Feb, 2022 10:12:55 AM then it should skip the Saturdays and Sunday in it but should give me anything above the whole days in decimal too. Right now I'm using below mentioned function to add the Column in my existing table.

Days Without Weekends = NETWORKDAYS(Start Date, End Date)-1

This above function is giving me output as 3.00. But I need any extra hours above 3 Days in decimals too.

Thanks in advance.
 
If you do a plain subtract of the start date & time (SD) from the end date and time (ED), you'll get 4.9532…, but you're only interested in the decimal part, so:
=MOD(ED-SD,1)
will give you the decimal portion of a day only.
If you want the result expressed as decimal hours, multiply by 24.
 
Thank you for your response.
I need a decimal number like 3.95(example) as output and not just the decimal. It should also exclude the weekends while calculating the output.
 
So add it to your NETWORKDAYS!

You'll have to make sure neither start nor end day is on a weekend.
 
Last edited:
But there are cases when either start date or end date is falling on a weekend. Is there a way through which we can achieve all the three targets (Excludes Weekends, Gives a Whole Number of days in addition to days in decimal).
 
You'll have to translate this worksheet formula to Power BI/DAX:
Code:
=MAX(0,NETWORKDAYS.INTL([@Start],[@End],1))-IF(WEEKDAY([@Start],2)<6,MOD([@Start],1),0)+IF(WEEKDAY([@End],2)<6,MOD([@End],1)-1,0)
It seems to give the right answers but you must check thoroughly.
 
Hi @p45cal - The code which you provided above is working absolutely fine in Power BI if we replace NETWORKDAYS.INTL with NETWORKDAYS. Thank you so much for the help.
 
Back
Top