Forum Discussion
Helper IAverage time between events
Hi all,
I have a table CIP_CIPStationConcentrates and I want to calculate the average time inbetween the replenishments (ActionID=11) for caustic tank (TankID = 1) from CIP Station 1 (there is only CIP Station 1 in my example, but in general there can be many). How to calculate it? I would expect the answer to be 11 days - since it's 2 days between first and second replenishment and 20 days between second and third.
Thank you!
Joanna
| ActionID | ActionName | ActionDate | TankID | TankName | Amount | CIP Station |
| 12 | Consumption | 2023.10.11 09:12 | 2 | CIP Acid tank | 4,0 | 1 |
| 11 | Replenishment | 2023.10.11 12:03 | 1 | CIP Caustic tank | 500,0 | 1 |
| 11 | Replenishment | 2023.10.11 12:04 | 2 | CIP Acid tank | 500,0 | 1 |
| 11 | Replenishment | 2023.10.11 12:07 | 5 | CIP Disinfectant | 500,0 | 1 |
| 12 | Consumption | 2023.10.11 12:12 | 1 | CIP Caustic tank | 5,3 | 1 |
| 12 | Consumption | 2023.10.11 12:12 | 5 | CIP Disinfectant | 4,5 | 1 |
| 12 | Consumption | 2023.10.11 13:12 | 1 | CIP Caustic tank | 5,3 | 1 |
| 12 | Consumption | 2023.10.11 14:20 | 1 | CIP Caustic tank | 4,7 | 1 |
| 12 | Consumption | 2023.10.11 16:12 | 1 | CIP Caustic tank | 5,6 | 1 |
| 12 | Consumption | 2023.10.11 19:20 | 1 | CIP Caustic tank | 6,1 | 1 |
| 12 | Consumption | 2023.10.11 21:12 | 1 | CIP Caustic tank | 4,3 | 1 |
| 12 | Consumption | 2023.10.11 22:20 | 1 | CIP Caustic tank | 2,7 | 1 |
| 12 | Consumption | 2023.10.12 00:12 | 1 | CIP Caustic tank | 3,7 | 1 |
| 12 | Consumption | 2023.10.12 05:12 | 1 | CIP Caustic tank | 2,8 | 1 |
| 12 | Consumption | 2023.10.12 07:12 | 1 | CIP Caustic tank | 2,1 | 1 |
| 12 | Consumption | 2023.10.12 09:13 | 2 | CIP Acid tank | 5,0 | 1 |
| 12 | Consumption | 2023.10.12 12:12 | 1 | CIP Caustic tank | 3,3 | 1 |
| 12 | Consumption | 2023.10.12 13:13 | 5 | CIP Disinfectant | 5,5 | 1 |
| 12 | Consumption | 2023.10.12 15:12 | 1 | CIP Caustic tank | 5,1 | 1 |
| 12 | Consumption | 2023.10.12 19:12 | 1 | CIP Caustic tank | 5,0 | 1 |
| 12 | Consumption | 2023.10.12 21:12 | 1 | CIP Caustic tank | 5,4 | 1 |
| 12 | Consumption | 2023.10.13 00:12 | 1 | CIP Caustic tank | 4,3 | 1 |
| 12 | Consumption | 2023.10.13 03:12 | 1 | CIP Caustic tank | 3,9 | 1 |
| 12 | Consumption | 2023.10.13 06:12 | 1 | CIP Caustic tank | 4,3 | 1 |
| 12 | Consumption | 2023.10.13 09:12 | 1 | CIP Caustic tank | 6,1 | 1 |
| 12 | Consumption | 2023.10.13 09:14 | 2 | CIP Acid tank | 6,0 | 1 |
| 11 | Replenishment | 2023.10.13 12:03 | 1 | CIP Caustic tank | 499,0 | 1 |
| 12 | Consumption | 2023.10.13 14:14 | 5 | CIP Disinfectant | 6,5 | 1 |
| 12 | Consumption | 2023.11.01 09:12 | 1 | CIP Caustic tank | 4,0 | 1 |
| 11 | Replenishment | 2023.11.02 12:03 | 1 | CIP Caustic tank | 500,0 | 1 |
Hi Kohrinn
You can create several calculated columns as follow.
Datediff = VAR _lastdate = CALCULATE ( MAX ( CIP_CIPStationConcentrates[ActionDate] ), FILTER ( CIP_CIPStationConcentrates, [ActionID] = EARLIER ( CIP_CIPStationConcentrates[ActionID] ) && [TankID] = EARLIER ( CIP_CIPStationConcentrates[TankID] ) && [CIP Station] = EARLIER ( CIP_CIPStationConcentrates[CIP Station] ) && [ActionDate] < EARLIER ( CIP_CIPStationConcentrates[ActionDate] ) ) ) RETURN DATEDIFF ( _lastdate, [ActionDate], DAY )Time = AVERAGEX ( FILTER ( CIP_CIPStationConcentrates, [ActionID] = EARLIER ( CIP_CIPStationConcentrates[ActionID] ) && [TankID] = EARLIER ( CIP_CIPStationConcentrates[TankID] ) && [CIP Station] = EARLIER ( CIP_CIPStationConcentrates[CIP Station] ) ), [Datediff] )Is this the result you expect?
Best Regards,
Community Support Team _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Kohrinn , Create a new column
New Column=
Var _max = maxx(filter(Table, Table[ActionID] = earlier(Table[ActionID]) && [TankID] <earlier([TankID])), [TankID])
return
if(isblank(_max), blank(),
datediff( maxx(filter(Table, Table[ActionID] = earlier(Table[ActionID]) && [TankID] =_max),[ActionDate]) , [ActionDate], day) )You can use Avg in measure
- Kohrinn
Something's not right.
There should be "2" in the secon row.
Hi Kohrinn
You can create several calculated columns as follow.
Datediff = VAR _lastdate = CALCULATE ( MAX ( CIP_CIPStationConcentrates[ActionDate] ), FILTER ( CIP_CIPStationConcentrates, [ActionID] = EARLIER ( CIP_CIPStationConcentrates[ActionID] ) && [TankID] = EARLIER ( CIP_CIPStationConcentrates[TankID] ) && [CIP Station] = EARLIER ( CIP_CIPStationConcentrates[CIP Station] ) && [ActionDate] < EARLIER ( CIP_CIPStationConcentrates[ActionDate] ) ) ) RETURN DATEDIFF ( _lastdate, [ActionDate], DAY )Time = AVERAGEX ( FILTER ( CIP_CIPStationConcentrates, [ActionID] = EARLIER ( CIP_CIPStationConcentrates[ActionID] ) && [TankID] = EARLIER ( CIP_CIPStationConcentrates[TankID] ) && [CIP Station] = EARLIER ( CIP_CIPStationConcentrates[CIP Station] ) ), [Datediff] )Is this the result you expect?
Best Regards,
Community Support Team _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Kohrinn
Thank you, that's exactly what I needed!
