Power BI - Get the number of days a date changes over the months

[obriaincian]

I have a dataset that has 3 columns Product, Expiry Date and Report Date.

Report Date	Product	Expiry Date
Dec-21	PPPT-01	25/01/2024
Dec-21	WEC-54	05/06/2023
Dec-21	ERRE-66	23/08/2025
Jan-22	PPPT-01	01/01/2024
Jan-22	WEC-54	05/06/2023
Jan-22	ERRE-66	23/08/2025
Feb-22	PPPT-01	01/01/2024
Feb-22	WEC-54	15/06/2023
Feb-22	ERRE-66	23/08/2025
Mar-22	PPPT-01	25/01/2024
Mar-22	WEC-54	15/06/2023
Mar-22	ERRE-66	23/08/2025

What I a trying to do is create a 4th column called difference. This will output the number of days (+/-) the expiry date has changed per product from the previous month.

The current forumula I'm using is this but it's not working, any suggestions would be appreciated.

difference=
sum(Data[Expiry Date])-CALCULATE(SUM(Data[Expiry Date]),PREVIOUSMONTH(Data[Expiry Date].[Date]))

[amitchandak · Accepted solution]

@obriaincian , Make sure report_date is date , a new column

new column =
var _date = eomonth([report_date,-1)
var _max = maxx(filter(Table, [Product] =earlier([Product]) && eomonth([report Date],0) =_date ), [Expiry Date] )
return
datediff(_max,[Expiry Date],day)