Forum Discussion

LuciD's avatar
LuciD
Frequent Visitor
4 years ago
Solved

Date filtering

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 numberExpiration date  Car numberExpiration dateValidity Check
X109201.01.2021  X109201.01.202104.04.2024
X109001.01.2021  X109001.01.202104.04.2024
X200001.04.2022  X200001.04.2022Expired. Please contact
X202101.05.2022  X202101.05.2022Expired. Please contact
X109002.04.2024  X109002.04.202402.04.2024
X109204.04.2024  X109204.04.202404.04.2024

 

Thanks in advance for any help

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

     

6 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    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's avatar
      LuciD
      Frequent Visitor
      4 years ago

      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's avatar
        goncalogeraldes
        Super User
        4 years ago

        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

  • LuciD's avatar
    LuciD
    Frequent Visitor
    4 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        LuciD
        Frequent Visitor
        4 years ago

        Hi, Yin  

         

        Thanks a lot for the solution! It works great!