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

Use DateAdd to calculate if statement on Expiry product

I have a field that contains expiry date for items, and another date that contains warning before expiry. This is important to make the stock controller who which product is about to expiry. 

I need to construct a good Dateadd calculation that will give three status against today, Expired, Closed to Expiry and Ok 

this is what we have done in SQL but reconstructing in Powerbi is proving a challenge. 

case
when dateadd(day,1, constants.today) >= PartLot.ExpirationDate then '*** EXPIRED!!!***'
when dateadd(day,PartClass.Alert1_c, constants.today) > PartLot.ExpirationDate then '*** CLOSED TO EXPIRY !!!***'
when dateadd(day,PartClass.Alert1_c + 400, constants.today) > PartLot.ExpirationDate then '*** OK!!!***'
else '*** CHECK EXPIRY DATE !!!***'
end

 

4 REPLIES 4
bwsutton1990 Regular Visitor
Regular Visitor

Re: Use DateAdd to calculate if statement on Expiry product

Hi

 

I just wanted to check, you want to do this in a custom column, rather than a measure right?

 

Thanks

Super User
Super User

Re: Use DateAdd to calculate if statement on Expiry product

@dniinoi  Please try something like this... 

 

IF(
    DATEADD(constants.today,1,DAY) >= PartLot.ExpirationDate,"*** EXPIRED!!!***",
	IF(DATEADD(constants.today,PartClass.Alert1_c,DAY) > PartLot.ExpirationDate,"*** CLOSED TO EXPIRY !!!",
		IF(DATEADD(constants.today,PartClass.Alert1_c+400,DAY) > PartLot.ExpirationDate, "*** OK!!!***")
	  )
  )
	


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





dniinoi Frequent Visitor
Frequent Visitor

Re: Use DateAdd to calculate if statement on Expiry product

this looks more like a sql function, formatting it to a DAX returns errors

dniinoi Frequent Visitor
Frequent Visitor

Re: Use DateAdd to calculate if statement on Expiry product

yes pls 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 307 members 3,312 guests