Forum Discussion
Finding Values if they historically exist
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 |
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.
3 Replies
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.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.
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 TeamIf 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
