topic DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life in DAX Commands and Tips

DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life

azoozaliraqe — Tue, 08 Oct 2024 04:40:21 GMT

Dear All ,

I need your help finding the proper way to apply this idea , I work in a pharmacutical manufacturer , every midecene we have obvisly have a Manufacturing date and Expiry date , I wanna know if this paticular product is less than two-thirds of it's shelf-life as on today or not .

Example , Manufacturing date of product A is March 1st 2024 , Expiry date is March 1st 2026 ,

less than two thirds will be any date from October 1st 2024 onwards.

As on today this product is less than two-thirds .

how to translate this idea into Power Bi.

Thanks alot ❤️

Re: DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life

nandukrishnavs — Tue, 08 Oct 2024 05:01:20 GMT

azoozaliraqe

Assuming that your table structure is similar to to below.

Then create a calculated column

Is Product Less than Two-thirds = VAR _shelflife = 'Table'[Expiry Date] - 'Table'[Manufacturing Date] VAR _remaing_days = 'Table'[Expiry Date] - TODAY () VAR _result = IF ( _shelflife * DIVIDE ( 2, 3 ) < _remaing_days, "Yes", "No" ) RETURN _result

Re: DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life

Kedar_Pande — Tue, 08 Oct 2024 05:23:04 GMT

Create a calculated column:

Is_Less_Than_Two_Thirds = 
IF(TODAY() >= 
[ManufacturingDate] + (DATEDIFF([ManufacturingDate], [ExpiryDate], DAY) * 2 / 3),
"Yes", 
"No"
)

Re: DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life

Anonymous — Wed, 09 Oct 2024 06:22:35 GMT

Hi azoozaliraqe

Thank you very much nandukrishnavs and Kedar_Pande for your prompt reply. Allow me to share some other ways.

In addition to calculating columns, you can also consider creating measures.

Is it less than two thirds = VAR _Shelflife = DATEDIFF(SELECTEDVALUE('Table'[Manufacturing Date]), SELECTEDVALUE('Table'[Expiry Date]), DAY) VAR _RemainingDate = DATEDIFF(TODAY(), SELECTEDVALUE('Table'[Expiry Date]), DAY) RETURN IF( DIVIDE(_RemainingDate, _Shelflife) >= DIVIDE(2, 3), "No less than two thirds", "Less than two thirds" )

Here is the result.

Regards,

Nono Chen

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

Re: DAX formula to mark if the Product is less than Two-Thirds of it's shelf-life

azoozaliraqe — Mon, 14 Oct 2024 04:09:57 GMT

Thanks alot dears , I have solved my problem thanks to your help.