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 multi...
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

     

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