Datediff workday pnly

DAX DAX Calculations
1

Hi,

I am trying to calculate the difference between two dates using Datediff but only count working day. The workday Y/N is in the Date Table, the other two dates is between today and order date.
When I use Datediff(todayutc,orderdate, days), the formula works fine. However I only want to count workdays. Can I do this with the Datediff function? Negative days is also counting hence I cannot use betweendates function.

Elizabeth

2

@ElizabethTachjian,

I thought we addressed this same question in the thread below last summer:

Here are some more videos and forum threads that also address this question in detail:

I hope this is helpful.

  • Brian
3

Hello Elizabeth

DATEDIF may not be an option here.

I suggest you could create a calculated column in your fact table (see below an example computing the working days between order and delivery dates):

WorkingDays = 
COUNTROWS(
    FILTER(
        ALL( 'Date'[Date] ),
        VAR
            WorkDays = NOT WEEKDAY( 'Date'[Date], 1 ) IN { 1, 7 }
        VAR
            Criterion = 
                'Date'[Date] >= 'Sales'[Order Date] &&
                'Date'[Date] <= 'Sales'[Delivery Date]
        RETURN
            WorkDays &&
            Criterion
    )
)

This calculation assumes the answer is between the Order Date and the Delivery Date (in your case, between the Order Date and today)

The measure does an average:
AVERAGE ( WorkingDays )

I attach and example below.

Example.pbix (511.4 KB)

4

Hi @ElizabethTachjian, we’ve noticed that no response has been received from you since the 5th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!