Forum Discussion

lucsilva's avatar
lucsilva
New Member
6 years ago
Solved

How to Add a calculated column based on a formula

I need to prepare a schedule for a team. In some cases I need to estimate the Start Date based on the last job and days of duration. Below are some exemples.

I could do this on excel using the formula "IF"... but how can I calculate the column "New Start Date" on Power BI?

 

Thank you

Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hi lucsilva ,

     

    So the last job is based on the latest start date per Team? Try this as a calculated column:

     

    New Start Date =
//get the max start date per team
CALCULATE (
    MAX ( 'Table'[Start Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Team] )
) //add days
+ 'Table'[Days]

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey lucsilva 

     

    What is your formula in excel? I believe what you arelooking for is the DATEADD function: https://docs.microsoft.com/en-us/dax/dateadd-function-dax

     

    You'll need some sort of helper column to sequence (number the rows) so the formula looks at the correct row even when sorted or pivoted since Power BI Data view does not operate exactly like excel.

     

    If this helps please kudo.

    If this solves your problem please accept it as a solution.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    Hi lucsilva ,

     

    How do you determine which one is the last job? Looking at the posted sample data, it doesn't seem to be based on Start Date.

    • lucsilva's avatar
      lucsilva
      New Member
      6 years ago
      Based on the Start Date column I know which one is the last job. For example, the last job of Team 01 (T 01) starts on 13/07/2020 (dd/mm/yyyy).
      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        6 years ago

        Hi lucsilva ,

         

        So the last job is based on the latest start date per Team? Try this as a calculated column:

         

        New Start Date =
//get the max start date per team
CALCULATE (
    MAX ( 'Table'[Start Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Team] )
) //add days
+ 'Table'[Days]