Forum Discussion

RY2019's avatar
RY2019
Frequent Visitor
4 years ago

DateADD working Days

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 :)!

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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's avatar
      RY2019
      Frequent Visitor
      4 years ago

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