Forum Discussion

AU555's avatar
AU555
Helper I
6 years ago
Solved

calculation within the same column

Hello everybody,

hope i can get help here with the formula for this i suppose easy calculation, but i just dont know how to solve this issue. 

I have this sample table and what I need to do is to calculate the difference between actual_finish field for each code when the same amount is installed. So when taking the first four rows I need to calculate 23/02/2019 - 13/02/2019 because both have the same installed amount and the code are not the same. So continuing, I need to calculate 24/02/2019 - 16/02/2019 because both have 2 amount installed and the code is kl1 at one date and kl2 at the second date.

  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    AU555 ,

     

    You can create a calculate column using dax below:

    Diff = 
VAR Earliest_Date = CALCULATE(MIN('Table'[Actual_Finish]), ALLEXCEPT('Table', 'Table'[amount installed]))
VAR Latest_Date = CALCULATE(MAX('Table'[Actual_Finish]), ALLEXCEPT('Table', 'Table'[amount installed]))
RETURN
DATEDIFF(Earliest_Date, Latest_Date, DAY)

     

     

    Community Support Team _ Jimmy Tao

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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    AU555 ,

     

    You can create a calculate column using dax below:

    Diff = 
VAR Earliest_Date = CALCULATE(MIN('Table'[Actual_Finish]), ALLEXCEPT('Table', 'Table'[amount installed]))
VAR Latest_Date = CALCULATE(MAX('Table'[Actual_Finish]), ALLEXCEPT('Table', 'Table'[amount installed]))
RETURN
DATEDIFF(Earliest_Date, Latest_Date, DAY)

     

     

    Community Support Team _ Jimmy Tao

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