Forum Discussion

Varan_15's avatar
Varan_15
Helper III
3 years ago

Time Difference based by workstep wise

Hi All,

 

I have data which is contains workstep (input, process, rejected, completed) i want to calculate the time differences between
(first input entry time - last completed exit time )

 

Example: same i have to get for each ID - highlighted expected result as below

 

 

Thanks,

Varan

Need Help

4 Replies

  • lin_k's avatar
    lin_k
    New Member
    3 years ago

    Hi Varan_15 

     

    in this case you can follow the lined out steps:

    1. Select Datatype as Date & Time (you can do this by right-clicking on the header of the column)

    2. Add a custom row where you subtract Entry from Extract time, simply do this by using "-" and writing a formular

     

    3. Format the result of your custom column as you need it 

     

    Let me know if this helps 🙂

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

     

    total minutes took measure: =
VAR _entrymin =
    MIN ( Data[Entry time] )
VAR _exitmax =
    MAX ( Data[Exit time] )
RETURN
    IF ( HASONEVALUE ( Data[ID] ), DATEDIFF ( _entrymin, _exitmax, MINUTE ) )

     

    • Varan_15's avatar
      Varan_15
      Helper III
      3 years ago

      Hi Jihwan_Kim ,

       

      I need to create calculated column if use above mentioned dax it's taking entire data in min/ max values but i need only workstep  input - min entry date - completed max exit date

       

      Thanks

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Please check the below picture and the attached pbix file.

        It is for creating a new column.

         

         

         

        Total minutes took CC =
VAR _inputentrymin =
    MINX (
        FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) && Data[Workstep] = "Input" ),
        Data[Entry time]
    )
VAR _completeexitmax =
    MAXX (
        FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) && Data[Workstep] = "Completed" ),
        Data[Exit time]
    )
RETURN
    IF (
        Data[Workstep] = "Completed",
        DATEDIFF ( _inputentrymin, _completeexitmax, MINUTE )
    )