Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RY2019
Frequent Visitor

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 2
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1648091125904.png

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors