DAX - Check number of months between 2 dates for each Product

[obriaincian]

Hi All,

I am relatively new to PBI and need some help.

I have a dataset which is similar to the below

Product	Expiry Date	Date
Coke	02/02/2023	Jan-21
Pepsi	23/08/2024	Jan-21
7up	01/01/2023	Jan-21
Fanta		Jan-21
Coke	02/02/2023	Feb-21
Pepsi	05/08/2024	Feb-21
7up	01/01/2023	Feb-21
Fanta	02/04/2023	Feb-21
Coke	08/05/2023	Mar-21
Pepsi	23/08/2024	Mar-21
7up	27/01/2023	Mar-21
Fanta	02/02/2023	Mar-21

What I want to do is to create a table that groups each Product and then take the min date from the "Date" column and the max date from the "Date" column  (in this case it would be Jan-21 and Mar-21) and then compare the corresponding Expiry Date rows for each product to see if the expiry date had changed and log the change in a "Changes" column

The desired output would be something like this:

Product	Changes
Coke	> 1 month change
Pepsi	No change
7up	<1 month change
Fanta	No Expiry Date

If an expiry date is missing for the min or max date return "No expiry date"

[v-xiaotang · Accepted solution]

Hi @obriaincian 

Try this, 

Measure = 
var _minDate= CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product]))
var _maxDate= CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Product]))
var _minED=  CALCULATE(MAX('Table'[Expiry Date]),FILTER( ALLEXCEPT('Table','Table'[Product]),'Table'[Date]=_minDate))
var _maxED=  CALCULATE(MAX('Table'[Expiry Date]),FILTER( ALLEXCEPT('Table','Table'[Product]),'Table'[Date]=_maxDate))
return SWITCH(TRUE(),
ISBLANK(_minED) || ISBLANK(_maxED), "No Expiry Date",
_maxED=_minED,"No change",
_maxED>EOMONTH(_minED,0),"> 1 month change",
_maxED<EOMONTH(_minED,0),"< 1 month change"
)

result

Best Regards,

Community Support Team _Tang

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

[PaulDBrown]

Is the Date field an actual date or is it text?

[obriaincian]

it is a date field