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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RonaldvdH
Post Patron
Post Patron

Formula to subtract days based on a date column

Ive such a simple question but im unable to fix it.

 

I need to plan an activity base upon another activity and therefore i need to subtract 14 days from a date column but the result returns a date in the year 1899

 

The formula is simple 

Sisu gepland = Adressen[civiel begin]-14
 
But the result is 50% correct because the date is good but the year wrong and actually i want to subtract 14 working days
2 ACCEPTED SOLUTIONS
ibarrau
Super User
Super User

Hi, it will depend in where you solve this. You have to create a new column. If you want to solve it in edit queries check "AddDays" function. If you want to solve this with DAX check "DATEADD" function. In order to make it work the column has to be date type. 

 

Hope this helps,

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


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

Happy to help!

LaDataWeb Blog

View solution in original post

@ibarrau ive corrected the problem and the right formula should be 

 

Sisu gepland = dateadd(Adressen[civiel begin].[Date];-14;DAY)
 
However do you know how to make sure it subtracts workingdays instead of just days because in this formula the date could be a saturday or sunday.
I have a 'date' table with a column in it to check if its a weekday

View solution in original post

4 REPLIES 4
ibarrau
Super User
Super User

Hi, it will depend in where you solve this. You have to create a new column. If you want to solve it in edit queries check "AddDays" function. If you want to solve this with DAX check "DATEADD" function. In order to make it work the column has to be date type. 

 

Hope this helps,

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


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

Happy to help!

LaDataWeb Blog

@ibarrau ive corrected the problem and the right formula should be 

 

Sisu gepland = dateadd(Adressen[civiel begin].[Date];-14;DAY)
 
However do you know how to make sure it subtracts workingdays instead of just days because in this formula the date could be a saturday or sunday.
I have a 'date' table with a column in it to check if its a weekday

Power bi by default won't help you with working days. This is something you have to create, do some data modeling first.

Check this post talking about them: https://www.sqlbi.com/articles/counting-working-days-in-dax/

Right now your formula just substract days in general.

 

Regards,

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


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

Happy to help!

LaDataWeb Blog

I have to substract two columns one date and other number .

want to substract date

eg

c1                                  c2            c3(expected)

21-08-2020                  11               07-08-2020

10-08-2020                   6                03-08-2020

17-08-2020                  13                30-07-2020

want c3 = c1-c2 excluding weekends ...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.