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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)