Check Car belong to Shop with Valid From - Valid to Date

[TeQ]

Hello,

I have some car that belong to Shop/ Profit Center. Car -Shop - Year

For example:

- Car "Renault 1" belong to shop London  from 01.01.2018 until today 31.12.2100

- Car "BMW 1" belong in year 2018,2019 to shop Paris with Valid 01.01.2018 -31.12.2019 and from Year 2020 - today to Berlin

01.01.2020 - 31.12.2100

- Car "BMW 2" Paris - New York- Berlin

- Car "Renault 2" with wrong Valid From- Valid to Date

- Car "Mercedes 1" with wrong Valid From - Valid to Date

How can i check and show the car with wrong Date (all rows of car "Renault 2" and "Mercedes 1")?

Expl. Data

https://www.dropbox.com/sh/7vd96atx66e7dcz/AAD7DfC6cUhX7EEg0JiqZzV6a?dl=0

thanks

[TeQ]

Hi @AllisonKennedy 

here is correct

Car belong until 31.12.2019 - to Paris

From 01.01.2020 - today - Berlin- there ist no gap in the Date

here is wrong

there is a gap from 09.012020  - 01.05.2020.

The report show me the wrong records - and that i know to correct it.

year 2019 - Mercedes 1 01.01.20218 - 09.01.2020

year 2020 - Mercedes 1 01.05.20220- 10.01.2020

[AllisonKennedy]

@TeQ  Looks like there's a few different scenarios. The one you described I have called "Move Shops", but there is also the case where it stays in the same shop multiple years in a row and therefore has the same validity dates, so I have called that "Renew"

Create new columns: 

Previous Valid To =

VAR _Car = Valid[Car]

VAR _Year = Valid[Year]

RETURN

MAXX(FILTER(Valid, Valid[Car] = _Car && Valid[Year] < _Year), Valid[Valid To])

Previous Valid From =

VAR _Car = Valid[Car]

VAR _Year = Valid[Year]

RETURN

MAXX(FILTER(Valid, Valid[Car] = _Car && Valid[Year] < _Year), Valid[Valid From])

Gap = DATEDIFF( Valid[Previous Valid To], Valid[Valid From], DAY)

Data Check =

SWITCH( TRUE()

, (Valid[Valid From] = Valid[Previous Valid From]) && (Valid[Valid To] = Valid[Previous Valid To]), "Renew"

, DATEDIFF(Valid[Previous Valid To], Valid[Valid From], DAY) = 1, "Move Shops"

, isblank(Valid[Previous Valid From]), "New"

, "Invalid"

)

Note you could combine these into one column, I have just split them so you can see what's happening.

Hope it helps.

[AllisonKennedy]

@TeQ  Sorry for the silly question, but can you please explain how you know it has a wrong date? Is there another table with valid conditions?