Forum Discussion

sovereignauto's avatar
sovereignauto
Icon for Helper III rankHelper III
2 years ago
Solved

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...
Need Help
Tips and Tricks
  • sovereignauto's avatar
    sovereignauto
    2 years ago

    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. 

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago
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.