DateADD working Days

[RY2019]

Hi all,

I´m currently working on a Power BI Report where Dax have to calculate the date of testing. First of all there is a start and a end date. The Formula should be (End date -Start date)- 1/3 Days from End Date. 

Some examples here what I want to see:  

Basic Start: date 18.07.2023

Finish Date: 07.08.2023

Formula: (07.08-18.07) = (15 Days) * 1/3 = 5 Days 

Testing Date: 07.08.2023 - 5 Days = 04.08.2023 (only Net Working Days)

I try to give this Formula in DAX but it doesn´t work correctly:
Test Date = DATEADD ('Database(Basic FIN)' [Finish date],((DATEDIFF('Database(Basic FIN)' [Finish date],'Database(Basic FIN)' [Start date],Day)/3))Day)

For some Dates I got the right Day but when it is for example more than 5 Days Power BI show me an empty Column.

Do you have an idea how to adjust the formula that it works correctly?

Thanks in Advance for support :)!

[v-yanjiang-msft]

Hi @RY2019 ,

The DATEADD function is a time intelligence function, there is a limitation about the function: "The result table includes only dates that exist in the dates column", it means when the result date isn't exist in the original data, it will return blank, more reference about this: DateAdd returns blank values - Microsoft Power BI Community

You can modify the formula like this:

Test Date =
'Database(Basic FIN)'[Finish date]
    + DATEDIFF (
        'Database(Basic FIN)'[Finish date],
        'Database(Basic FIN)'[Start date],
        DAY
    ) / 3

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[RY2019]

Thanks it works partially because there are some dates which end of a Saturday and Sunday and I only wanted that it gives me the Net Working Days (Monday - Friday)?