calculation within the same column

[AU555]

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 · Accepted solution]

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