Max Date in Data from 12 Months Ago

[mboucher_rcr]

Hi,

I have a measure that gives me the last date of available data that is then used in other measures:

Last date of data availability = MAX(ItemSales[WkStartDate])

What I need to do is get the same thing but for 12 months earlier. I don't want the same date minus 12 months, I still need a valid [WkStartDate] from my data but from 12 months earlier.

For example if my MAX(ItemSales[WkStartDate]) = 27th June 2022

I don't just want 27th June 2021, as that date doesn't exist as a [WkStartDate] value in my data. In my case, the dates are always a Monday, so I would expect to get 21st June 2021 as that's the highest date available when you take 12 months off the 27th June 2022.

Can anyone please help?

Thanks,
Michael

[amitchandak]

@mboucher_rcr , try like

new measure =
var _max1 = MAX(ItemSales[WkStartDate])
var _max = date(year(_max1)-1, Month(_max1) , Day(_max1))
return
maxx(filter(Allselected(ItemSales), ItemSales[WkStartDate] <=_max) , ItemSales[WkStartDate])

[mboucher_rcr]

Hi,

Thanks for that. I've just tried it and am getting this error: A single value for column 'WkStartDate' in table 'ItemSales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum...

Thanks,
Michael