Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LuciD
Frequent Visitor

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

1 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"
    )

yingyinr_0-1657776060375.png

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

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
LuciD
Frequent Visitor

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. 

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"
    )

yingyinr_0-1657776060375.png

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

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, @Yin  

 

Thanks a lot for the solution! It works great! 

goncalogeraldes
Super User
Super User

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

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?

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.