Finding Values if they historically exist

[sovereignauto]

Good Morning, 

Not sure how to phrase this so will give it my best shot.

Below is a table that shows this vehicle was on hire with lic number 2888 4 times but there was some other lic numbers in the middle. 

what im looking to do is somehow identify that thsi has happend and flag the rows in which this happend. 

the key here is there must be a "split" in the licence to flag it. 

by "flag" this could just be a column with "yes" or "1" or to be honest anything in it. 

Actual Collection DateActual Delivery DateLicensing AuthorityLicenceStart DateEnd DateLicence Type (Licence) (Licence)

	04/01/2024	LIC-004705	18/09/2023	17/09/2024	Annual
29/12/2023	30/10/2023	LIC-003993	05/06/2023	04/06/2024	Annual
27/10/2023	14/09/2023	LIC-002888	13/12/2022	13/12/2023	Annual
08/09/2023	01/08/2023	LIC-002888	13/12/2022	13/12/2023	Annual
10/07/2023	05/06/2023	LIC-003993	05/06/2023	04/06/2024	Annual
30/05/2023	20/03/2023	LIC-003522	22/03/2023	18/05/2023	Annual
10/03/2023	17/02/2023	LIC-003289	27/02/2023	26/02/2024	Annual
23/01/2023	11/01/2023	LIC-003010	11/01/2023	11/03/2023	Transfer
30/01/2023	25/01/2023	LIC-002888	13/12/2022	13/12/2023	Annual
20/12/2022	14/12/2022	LIC-002888	13/12/2022	13/12/2023	Annual

[sovereignauto · Accepted solution]

i Got it working in excel in the end.

I found the last time the licence was used prior to the current and then coutn how many (if any) ither records there were in between, if none then didnt flag it if there was then i flagged it. 

[v-yaningy-msft]

Hi, @sovereignauto 

May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

Tag =

VAR _Counts =

    CALCULATE (

        COUNTROWS (

            FILTER (

                'Table',

                'Table'[Licensing Authority] = SELECTEDVALUE ( 'Table'[Licensing Authority] )

            )

        ),

        ALLEXCEPT ( 'Table', 'Table'[Licensing Authority] )

    )

RETURN

    IF ( _Counts > 1, "yes" )

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

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 in the Power BI Forum

[vicky_]

Has Prev = INT(CALCULATE(
    COUNTROWS('Table (2)'), 
    FILTER(ALL('Table (2)'), 
        'Table (2)'[Licensing Authority] = EARLIER('Table (2)'[Licensing Authority]) && 
        'Table (2)'[Actual Delivery Date] < EARLIER('Table (2)'[Actual Delivery Date])
    )
) > 0)

Hey the above is what i've come up with - the basic idea is to check if the same license has an "actual delivery date" earlier than the current row's, and then return 1 if that's true.