Calculating YOY Change Dynamic Date Ranges - Different End Dates

[Anonymous]

Hello - 

In a table [Dates], I have a list of products with different modification dates.

I have the live date, live date - 1 year, and live date + 1 year.

I have used these dates in calculations from another table [Products] to calculate the unit sales associated with the year before the mod, a year after the mod, and % change (calculations below).

units1yearprior =

VAR prod = MAX(Products[ProdDesc])

VAR dateprior = CALCULATE(MIN('Dates'[LIVE DATE -1]), FILTER('Dates', 'Dates'[Product] = prod))

VAR dateend = CALCULATE(MAX('Dates'[LIVE DATE]), FILTER('Dates', 'Dates'[Product] = prod))

RETURN

CALCULATE(SUM(Products[Unit Sales Total]), FILTER(Products, Products[Date] >= dateprior &&Products[Date] <= dateend && Products[ProdDesc]=prod ))

units1yearafter =

VAR prod = MAX(Products[ProdDesc])

VAR dateprior = CALCULATE(MIN('Dates'[LIVE DATE]), FILTER('Dates', 'Dates'[Product] = prod))

VAR dateend = CALCULATE(MAX('Dates'[LIVE DATE +1]), FILTER('Dates', 'Dates'[Product] = prod))

RETURN

CALCULATE(SUM(Products[Unit Sales Total]), FILTER(Products, Products[Date] >= dateprior &&Products[Date] <= dateend && Products[ProdDesc]=prod ))

Units ∆ = ([units1yearafter]-[units1yearprior])/[units1yearprior]

The issue is (if you take Product A as an example) - we will have a full years worth of data for 4/2020-4/2021 but not a full year for 4/2021-4/2022 (as it has not occurred yet).  I want to be able to get an accurate read of the unit sales change since the modification and would like to compare just the months that have occurred thus far for each product (i.e. for Product A: 4/2020-6/2020 vs 4/2021-6/2021)

Please help!

[Anonymous · Accepted solution]

Team -

I spent the good portion of the last day working through various solutions but, figured it out (DAX below):

PY Unit Sales (Same Period) =

VAR prod = MAX(Products[ProdDesc])

VAR dateprior = CALCULATE(MIN('Dates'[LIVE DATE -1]), FILTER('Dates', 'Dates'[Product] = prod)

VAR enddate= CALCULATE(IF(DATEADD(LASTDATE(Product[Date]),-1,YEAR)<MAX('Dates'[LIVE DATE]),DATEADD(LASTDATE(Product[Date]),-1,YEAR),MAX('Dates'[LIVE DATE])),FILTER(Product,Product[ProdDesc]=prod))

RETURN

CALCULATE(

[Unit Sales],DATESBETWEEN(Product[Date],dateprior,enddate))