Calculate datediff of many status in a events table

[Anonymous]

Hi all

I got a table of status changes. I've been searching but I don't find any solutions that fits my data.

I have a table of Commands, this stores when a translation batch is genereated and when it is imported. Genaration will be at once but importation could be in diferent times.

this is a small sample of the table:

Id	CommandType	CommandDescription	CreatedOn	Duration
37650433	9	Script nº7, Session_One	6/11/2019 13:21
37158913	9	Script nº7, Session_One	6/5/2019 13:00
36962305	9	Script nº7, Session_One	6/5/2019 10:19
36765697	9	Script nº7, Session_One	6/5/2019 9:47
36339714	9	Script nº7, Session_One	6/4/2019 8:39
36077569	9	Script nº7, Session_One	5/31/2019 15:41
35979269	9	Script nº7, Session_One	5/31/2019 14:26
35979268	9	Script nº7, Session_One	5/31/2019 14:20
35979267	9	Script nº7, Session_One	5/31/2019 14:18
35979266	9	Script nº7, Session_One	5/31/2019 14:17
35979265	9	Script nº7, Session_One	5/31/2019 14:14
35291137	8	Script nº7, Session_One	5/28/2019 14:32
33783809	6	Batch nº1, B001_VO	5/21/2019 16:06	12
33783808	6	Batch nº1, B001_VO	5/21/2019 16:00	12
33390592	5	Batch nº1, B001_VO	5/9/2019 10:22

The CommandType is the action itself:

• 5 = generate batch
• 6 = import batch
• 8 = generate script
• 9=import script

Command description is used for diferent batches or scripts. As you can generate many of them. So the duration should be calculated matching the same batch or script.

CreatedOn is the date when the event have been done.

My aproach is add a "duration" column that will be filed in the import events. it will look for the corresponding generetion event by matching description column and calculate a datediff funcion. Like this:

Duration =
IF (
    Commands[CommandType] = 6,
    DATEDIFF (
        LOOKUPVALUE (
            Commands[CreatedOn],
            Commands[CommandDescription], Commands[CommandDescription],
            Commands[CommandType], 5
        ),
        Commands[CreatedOn],
        DAY
    ),
    BLANK ()
)

But I have many status like this in all the production chain, adding all of them will become a huge if sentence than I don't think it will be efficient.

I tried to add more status like this but it won't work.

Duration =
IF (
    Commands[CommandType] = 6
        || 9,
    DATEDIFF (
        LOOKUPVALUE (
            Commands[CreatedOn],
            Commands[CommandDescription], Commands[CommandDescription],
            Commands[CommandType], Commands[CommandType] IN { 5, 8 }
        ),
        Commands[CreatedOn],
        DAY
    ),
    BLANK ()
)

becasue of this  Commands[CommandType] IN { 5, 8 }  in the Lookupvalue is not accepted for values to serach.

Please if someone has an idea for making this it'd be a huge help.

[Cmcmahan · Accepted solution]

So to put this in simpler terms:

• You have a list of events with an Id, Description, command type, and timestamp.
• You want a new column, that calculates a duration for each event where the command type equals 6, and otherwise is left blank
• When a row with command type 6 is found, this column should look for another event with a matching description and command type equals 5
• It should then calculate a datediff between the timestamps on the two rows in question

So my questions to make sure we get to a solution that will work are:

• Is the Description for each batch unique? If not, how should I determine which type 5 command to calculate a duration from?
• Does each batch always have exactly one type 5 command, or is it possible to have zero type 5 commands, or possible to have multiple type 5 commands?
• Do you really need this as a calculated column, or would a measure work for your purposes?  A calculated column will make your data import take longer, but this value would be set every time you refresh.  A measure would be calculated on the fly, as you need it, and wouldn't clutter your data table.  I would suggest using a measure if it works for your data model.

Here's how I would create the measure, assuming that descriptions are unique AND that every 6 event will have exactly one matching 5 event. If this is not the case, follow up here with what's different and we can modify the measure.

Duration = 
IF( SELECTEDVALUE(Commands[CommandType]) = 6,
    DATEDIFF( 
        CALCULATE( 
            SELECTEDVALUE(Commands[CreatedOn]), 
            FILTER( 
                ALL(Commands), 
                Commands[CommandDescription] = SELECTEDVALUE(Commands[CommandDescription]) && 
                Commands[CommandType]=5
            )
        ), 
        SELECTEDVALUE(Commands[CreatedOn]), 
        DAY
    ) 
)

With your data, that ends up looking like this once you the 4 fields and 1 measure into a table visualization:

[Anonymous]

Hi @Cmcmahan 

Yes your assumptions are correct. It could be a generation batch = 5 and a reverted batch = 7 or imported = 6 but it can't exist an importation without generation. I dont care about reverted ones, thats why I approached looking for importation events.

The measure works fine, but the big poitn now is, if I want to add now the same proces for scripts that are commandstype 8 for generation and 9 for importation, and so on with more statuses. Shall I concatenate if sentences one into the other? like this?

Duration = 
IF( SELECTEDVALUE(Commands[CommandType]) = 6,
    DATEDIFF( 
        CALCULATE( 
            SELECTEDVALUE(Commands[CreatedOn]), 
            FILTER( 
                ALL(Commands), 
                Commands[CommandDescription] = SELECTEDVALUE(Commands[CommandDescription]) && 
                Commands[CommandType]=5
            )
        ), 
        SELECTEDVALUE(Commands[CreatedOn]), 
        DAY
    ),
    IF( SELECTEDVALUE(Commands[CommandType]) = 9,
        DATEDIFF( 
            CALCULATE( 
                SELECTEDVALUE(Commands[CreatedOn]), 
                FILTER( 
                    ALL(Commands), 
                    Commands[CommandDescription] = SELECTEDVALUE(Commands[CommandDescription]) && 
                    Commands[CommandType]=8
                )
            ), 
            SELECTEDVALUE(Commands[CreatedOn]), 
            DAY
        )
    )
)

[Cmcmahan]

Exactly. There might be cleaner ways to do it using a SWITCH statement, but that structure will definitely get the job done

[Anonymous]

Hi @Cmcmahan 

The meassuer do it's job great but I dont' know if i'm using it right. Finally I did one meassure for each of the statuses that I want to track. It work perfect even for status that mix details like the CommandDescription and an ID for audio files that I got into this table with Related() function:

InPostpro = 
IF( SELECTEDVALUE(CommandSnapshots[CommandType]) = 4,
    DATEDIFF( 
        CALCULATE( 
            SELECTEDVALUE(CommandSnapshots[CommandDate]), 
            FILTER( 
                ALL(CommandSnapshots), 
                CommandSnapshots[LocID] = SELECTEDVALUE(CommandSnapshots[LocID]) && 
                CommandSnapshots[CommandType]=27
            )
        ),
        SELECTEDVALUE(CommandSnapshots[CommandDate]), 
        DAY
    )
)

over this meassures I did anothe meassure with to get an average:

InPostpro Avg = CALCULATE(AVERAGEX(CommandSnapshots,[InPostpro]),ALL(CommandSnapshots))

I was willing to be able to get averages when crossed this averga with another table that contains meatdata like "type of postpro" but my surprise is that I got the same average for every type.

In order to do this would it be better get the Duration in a column into the table rather than in a meassure?

Thanks for your patience

[Cmcmahan]

Since you are using ALL(CommandSnapshots), I'm not surprised that you get the same result every time. You can read this thread for more information on what's happening. 

If you're trying to get the average duration for the entire dataset, then you should expect the same result each time.  If you're trying to average some subgroup, you have to tell the calculation to keep that subgroup.

InPostpro Avg = CALCULATE(AVERAGEX(CommandSnapshots,[InPostpro]),ALLEXCEPT(CommandSnapshots, CommandSnapshots[Type of postpro]))

[Anonymous]

Ohh I see!! Thanks for this fast response.

So As I understood with filter ALL(commandsnapshot) for InPostpro the average will drop dramatically as it will ponderate in the averge assets that have not been InProstpro status with a duration of Zero and included in the aritmetic average. That explains the numbers.

Thanks again!

🙂