Forum Discussion

RonaldvdH's avatar
RonaldvdH
Icon for Post Patron rankPost Patron
6 years ago
Solved

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...
Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    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.

  • RonaldvdH's avatar
    RonaldvdH
    6 years ago

    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
ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
6 years ago

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.

  • RonaldvdH's avatar
    RonaldvdH
    Icon for Post Patron rankPost Patron
    6 years ago

    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
    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      6 years ago

      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.

      • vijaykumarj19's avatar
        vijaykumarj19
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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 ...