How to get the latest value within a given date range from another column?

[Brij]

Hi,

I'm trying to solve a scenario where I can return a maximum value (based on 'end date') from another column that has an outcome. See the below example;

ID	End Time	Outcome
SUB001	6/10/2022 13:23	Approved
SUB001	29/09/2022 15:24	Reassign
SUB002	16/09/2022 11:18	Approved
SUB002	12/09/2022 16:33	Suspended
SUB002	9/09/2022 11:25	Suspended

In the above scenario, I have two IDs - SUB001 and SUB002. 

Ask - When I pick date period from 1st September 2022 to 30th September 2022 I should get total ID count 2,

1 under 'Approved' and 1 under 'Reassign' as for SUB002 all outcome falls in the same period as I have given here so it picks up the latest outcome and only that ID is counted. Whereas with SUB001, there are two oucome but the latest outcome is falling outside of my given period, that leaves only one record for the given period hence it is counted 1 under 'Reassign'

If I pick period from 25th September to 10th October in the above data table then it should return total ID count 1 under ' Approved' as both outcome from SUB001 falling within this period but it needs to pick up only the latest one that is end time = 6/10/2022 13:23 and none of the outcome from SUB002 falling with this date range!

I hope I have clearly explained what I am looking for. 

If someone can help me out would be great.

Thank you in advance.

Brij 

[v-jialluo-msft]

Hi Brij ,

Please follow these steps:

1.Create a measure to count the number of up-to-date states of all IDs

count =

VAR __ID =
    MAX ( 'Table'[id] )
VAR __TABLE =
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[id] = __ID )
VAR __MAX_TIME =
    MAXX ( __TABLE, 'Table'[EndTime] )
VAR __OUTCOME =
    MAXX (
        FILTER ( __TABLE, 'Table'[id] = __ID && 'Table'[EndTime] = __MAX_TIME ),
        'Table'[Outcome]
    )
VAR __COUNT =
    SUMX (
        FILTER (
            'Table',
            'Table'[id] = __ID
                && 'Table'[EndTime] = __MAX_TIME
                && 'Table'[Outcome] = __OUTCOME
        ),
        DISTINCTCOUNT ( 'Table'[id] )
    )
RETURN

    __COUNT

2.Final output

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Gallen Luo

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

[amitchandak]

@Brij , refer if my blog on a similar topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Brij]

Hi @amitchandak 

Thanks for your quick response.

I have tried your solution and I think it's very close to what I am looking for but it is not quite there what I am looking for!

I guess I was not clear enough on my problem statement.

What I need is to get the count of all IDs for the latest outcome for a given period. The requirement is to provide volume  (count of IDs) for each latest outcome for the selected date range.