cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dniinoi Frequent Visitor
Frequent Visitor

need a measure that compares dates and display status, Expired, Not Expired and Close to Expiry

hello Team,

I need to perform a measure in Powerbi that compare dates and give me results (Expiry Status)
A. Expired Status; Compare todays date with Expiry date field and display Status
B. Close to Expiry; Compare todays date against a field (Number of days to expire) and display status (Close to Expirey /Okay Not expired)

Since this is perishable stock, there is a table with field that has entered with number of days to warn before expiry and this
is important to factor when building this measure. 

 

thank you so much 

1 ACCEPTED SOLUTION

Accepted Solutions
jthomson New Contributor
New Contributor

Re: need a measure that compares dates and display status, Expired, Not Expired and Close to Expiry

I'm assuming that you've got some sort of relationship between your stock table and the table that holds your expiry warning cutoff times, one approach would be to:

 

- pull in the number of days that relates to that stock item as a variable

- use that number with TODAY() to work out when your warning date is

- make a nested if statement, seeing first if today's after the expiry date, then assigning a status of expired if it is, else seeing if today is after the warning date, then assigning a status of close to expired, otherwise not expired

View solution in original post

1 REPLY 1
jthomson New Contributor
New Contributor

Re: need a measure that compares dates and display status, Expired, Not Expired and Close to Expiry

I'm assuming that you've got some sort of relationship between your stock table and the table that holds your expiry warning cutoff times, one approach would be to:

 

- pull in the number of days that relates to that stock item as a variable

- use that number with TODAY() to work out when your warning date is

- make a nested if statement, seeing first if today's after the expiry date, then assigning a status of expired if it is, else seeing if today is after the warning date, then assigning a status of close to expired, otherwise not expired

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)