Calculated column to get the start and end date time

[Anonymous]

Hi, We have the below table data. Application ID, Stage no, Status and Date Time.

Each applcation will have multiple stages to go and there will be reversals too. In the below table you could see after stage 3 it again came to stage 2 and same happens after stage 4 too.

Each set of stage will have a start status and complete status with date and time. If it is not having complete status, then it means it is still in progress. 

Application ID	Stage	Status	Date Time
123	1	Start	09-08-2021 12:00
123	1	Complete	09-08-2021 13:00
123	2	Start	09-08-2021 14:00
123	2	Complete	09-08-2021 15:00
123	3	Start	09-08-2021 16:00
123	3	Complete	09-08-2021 17:30
123	2	Start	09-08-2021 18:00
123	2	Complete	09-08-2021 19:30
123	3	Start	09-08-2021 20:00
123	3	Complete	09-08-2021 21:00
123	4	Start	09-08-2021 22:00
123	4	Complete	09-08-2021 22:30
123	3	Start	09-08-2021 23:00

I need to frame the above table like below given table.

Application ID	Stage	Start Date Time	Completed Date Time
123	1	09-08-2021 12:00	09-08-2021 13:00
123	2	09-08-2021 14:00	09-08-2021 15:00
123	3	09-08-2021 16:00	09-08-2021 17:30
123	2	09-08-2021 18:00	09-08-2021 19:30
123	3	09-08-2021 20:00	09-08-2021 21:00
123	4	09-08-2021 22:00	09-08-2021 22:30
123	3	09-08-2021 23:00	Current Date Time

Each set of application and stage should be in same row with start and complete date time. Eventhough the application has duplicate stages in it the Date time fields will always be unique since it is a stage approval process.

Please help.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, I did the following test:

Group =
COUNTX (
    FILTER (
        ALL ( Sheet1 ),
        [Status] = "Start"
            && [DateTime] <= EARLIER ( Sheet1[DateTime] )
            && [Stage] = EARLIER ( Sheet1[Stage] )
    ),
    [Stage]
)

M =
VAR _start =
    CALCULATE (
        MAX ( 'Sheet1'[DateTime] ),
        FILTER (
            ALL ( Sheet1 ),
            [Stage] = MAX ( 'Sheet1'[Stage] )
                && [Group] = MAX ( 'Sheet1'[Group] )
                && [Status] = "Start"
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Sheet1'[DateTime] ),
        FILTER (
            ALL ( Sheet1 ),
            [Stage] = MAX ( 'Sheet1'[Stage] )
                && [Group] = MAX ( 'Sheet1'[Group] )
                && [Status] = "Complete"
        )
    )
RETURN
    _start & "-"
        & IF ( _end = BLANK (), NOW (), _end )

Best Regards,
Henry

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

[amitchandak]

@Anonymous , based on what I got so far

Unpivot last two column power query

https://radacad.com/pivot-and-unpivot-with-power-bi

Or create a table in dax

summarize(Table, [Application ID],[Stage], "Start Date", maxx(filter(Table, Table[Status] = "Start" ),[Date Time]), "End Date", maxx(filter(Table, Table[Status] = "Complete" ),[Date Time]))

or used the last column in above table as measures in a visual

[Anonymous]

@amitchandak  This DAX does not works...it excludes the duplicate stages.

Check this sample PBIX - https://1drv.ms/u/s!Ah-kO6CqeTNOgkHMByZiYJXGaeSO?e=Y4cc6r