Forum Discussion

Kohrinn's avatar
Kohrinn
Icon for Helper I rankHelper I
2 years ago
Solved

Average 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 on...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 _Yuliax

     

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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's avatar
Kohrinn
Icon for Helper I rankHelper I
2 years ago

Something's not right.

 

 

There should be "2" in the secon row.