Date filtering

[LuciD]

Hi, everyone!

I'm relatively new to Power Bi and was having trouble with implementing a date filter.

I have a database with car`s number plate and expiration date of ITP. A car may have multiple end of date for ITP

(example databese : X1092  - end date 01.01.2021; X1090 - end date 01.01.2021; X1092 - end date 04.04.2024 - the results must be 04.04.2024 for all the lines where the X1092 car`s number appear - see the table below)

The task is to have a new column with a date filter that check (in all the database) if a car number has an ITP valid until (bring the Max end date for that car number) . If it expired vs Today (like daily), need to see a message, something like "Expired. Please contact"

My database			My needs
Car number	Expiration date			Car number	Expiration date	Validity Check
X1092	01.01.2021			X1092	01.01.2021	04.04.2024
X1090	01.01.2021			X1090	01.01.2021	04.04.2024
X2000	01.04.2022			X2000	01.04.2022	Expired. Please contact
X2021	01.05.2022			X2021	01.05.2022	Expired. Please contact
X1090	02.04.2024			X1090	02.04.2024	02.04.2024
X1092	04.04.2024			X1092	04.04.2024	04.04.2024

Thanks in advance for any help

[LuciD]

Hi, @goncalogeraldes 

I tried as a measure, as u suggest. I dind`t received an error, but it brings me the same date as "Expiration date" . Doesn`t check in my database for a max date for that car number. 

[v-yiruan-msft · Accepted solution]

Hi @LuciD ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

Validity Check = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Expiration date] ),
        FILTER ( 'Table', 'Table'[Car number] = EARLIER ( 'Table'[Car number] ) )
    )
RETURN
    IF (
        DATEDIFF ( TODAY (), _maxdate, DAY ) > 0,
        FORMAT ( _maxdate, "dd.mm.yyyy" ),
        "Expired. Please contact"
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards

[LuciD]

Hi, @Yin  

Thanks a lot for the solution! It works great! 

[goncalogeraldes]

Hello there @LuciD ! Hope the following works for you:

Validity Check = 
var __max_date = 
    CALCULATE(
        MAX('Your_table'[Expiration Date]),
        'Your_table'[Car Number] = SELECTEDVALUE('Your_table'[Car Number])
    )

return
SWITCH(
    TRUE(),
    TODAY() > __max_date, __max_date,
    TODAY() = __max_date, "Expires today. Please contact",
    "Expired. Plase contact"
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[LuciD]

Hi @goncalogeraldes 

Thanks for the quickly reply!

I tried ur solution but I received an Error "Expressions that yield variant data-type cannot be used to define calculated columns. 

I want to mention that my calendar date column if formatted as "Date" and the "Car Number" column is formatted as "Text" . What should I do? 

I need a LOOKUPVALUE formula so I can populate the whole column "Validity check" for all my database?

[goncalogeraldes]

Hello there @LuciD ! Since you are trying to use both datevalue and text value in one calculate column,  Power BI can't auto analyse the data type for this calculated column. Instead of having this as a calculated column, why not have it as a measure? Is there any reason that prevents you from having this as a measure?

For additional help, please @ me in your reply!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes