DATEDIFF without weekend is giving wrong outputs

[Spartanos]

I have created a table which contains two dates, I just want to calculate the difference between them. Please see the file:

https://drive.google.com/file/d/17bisKyaR0DaM9A6wPrnyMwvi5StFPbMq/view?usp=sharing

I don't understand why the measure 'time in transit' is shorter than the measure 'Time in transit (without weekend).

I created a seperate table with all the days as of 2022-01-01.

[Spartanos]

Hi, this works, thanks. But I want to adjust it a little bit. If the manifest date is for instance 23/02/2022 and Date delivered is 24/02/2022, I want that time in transit  is just one day,  for both measures.  So I have to do for both measures -1 day?

[Fowmy · Accepted solution]

@Spartanos 
If you want to omit one day from the date, is from the manifest or delivery date? What if you have the same date for both?  However, I change the two formauls as follows, please check

Time in Transit = 
IF(
    [Manifest Date] < [Date Delivered],
    DATEDIFF(
        [Manifest Date],[Date Delivered],DAY
    ) 
)

Time in Transit (Without weekend) = 
IF(
    [Manifest Date] < [Date Delivered],
    COUNTROWS(
        FILTER(
            CALENDAR( [Manifest Date] + 1,  [Date Delivered] ),
            WEEKDAY([Date],2) < 6
        )
    )

)

[Fowmy]

@Spartanos 
I modifed the formula in both the columns, please check and let me know:

Time in Transit = 
IF(
    [Manifest Date] <= [Date Delivered],
    DATEDIFF(
        [Manifest Date],[Date Delivered],DAY
    ) + 1
)

Time in Transit (Without weekend) = 
IF(
    [Manifest Date] <= [Date Delivered],
    COUNTROWS(
        FILTER(
            CALENDAR( [Manifest Date] , [Date Delivered] ),
            WEEKDAY([Date],2) < 6
        )
    )
)

File is attached below