How to find the latest value within a set of dates

[zinminthant]

Hello,

I have below table and couldn't find a way to find Activity where NPT happens. Please help to find the NPT Main Activity Column. 

Start Time	End Time	Main Category	Activity	NPT Type	NPT Main Activity
22-Jan-22 0:00	23-Jan-22 23:15	Main Stage	Weekly Drill
22-Jan-22 20:15	22-Jan-22 22:15			Weather	Weekly Drill
23-Jan-22 23:15	24-Jan-22 2:30	Main Stage	Daily Meeting
24-Jan-22 2:30	26-Jan-22 6:25	Main Stage	Daily Meeting
24-Jan-22 2:30	24-Jan-22 3:50			Weather	Daily Meeting
24-Jan-22 3:50	24-Jan-22 4:50			Facility	Daily Meeting

Best Regards,

Zin. 

[v-cgao-msft · Accepted solution]

Hi @zinminthant ,

Please try creating a new calculated column.

NPT Main Activity = 
VAR _value_1 =
    IF ( 'Table'[NPT Type] <> BLANK (), 'Table'[Start Time] )
VAR _value_2 =
    CALCULATE (
        MAX ( 'Table'[Start Time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Activity] <> BLANK () ),
        FILTER ( ALL ( 'Table' ), 'Table'[Start Time] < _value_1 )
    )
VAR _value_3 =
    CALCULATE (
        MAX ( 'Table'[End Time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Start Time] = _value_2 )
    )
RETURN
    CALCULATE (
        MIN ( 'Table'[Activity] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Start Time] = _value_2 ),
        FILTER ( ALL ( 'Table' ), 'Table'[End Time] = _value_3 )
    )

The result should be like this.

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[zinminthant]

@v-cgao-msft 

Thank you. It is really great. 

Best Regards,

Zin. 

[zinminthant]

Hi @VahidDM 

Below is original table. NPT Type Start and End time falls inside the Main Stage Start and End time, and I would like to find out which Main Category and Activity it belongs to. 

Start Time	End Time	Main Category	Activity	NPT Type
22-Jan-22 0:00	23-Jan-22 23:15	Main Stage	Weekly Drill
22-Jan-22 20:15	22-Jan-22 22:15			Weather
23-Jan-22 23:15	24-Jan-22 2:30	Main Stage	Daily Meeting
24-Jan-22 2:30	26-Jan-22 6:25	Main Stage	Daily Meeting
24-Jan-22 2:30	25-Jan-22 6:25	Sub Stage	Walkaround
24-Jan-22 2:30	24-Jan-22 3:50			Weather
24-Jan-22 3:50	24-Jan-22 4:50			Facility

Below table is what I expect with new column called NPT Main Activity. 

Start Time	End Time	Main Category	Activity	NPT Type	NPT Main Activity
22-Jan-22 0:00	23-Jan-22 23:15	Main Stage	Weekly Drill
22-Jan-22 20:15	22-Jan-22 22:15			Weather	Weekly Drill
23-Jan-22 23:15	24-Jan-22 2:30	Main Stage	Daily Meeting
24-Jan-22 2:30	26-Jan-22 6:25	Main Stage	Daily Meeting
24-Jan-22 2:30	25-Jan-22 6:25	Sub Stage	Walkaround
24-Jan-22 2:30	24-Jan-22 3:50			Weather	Daily Meeting
24-Jan-22 3:50	24-Jan-22 4:50			Facility	Daily Meeting

Thanks in advance for your kind help. 

Best Regards,

Zin. 

[VahidDM]

Hi @zinminthant 

Can you please add more details to your question?? Please add the expected result table as per your sample data to make it clear.

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/