Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mboucher_rcr
Frequent Visitor

Max Date in Data from 12 Months Ago

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
2 REPLIES 2
amitchandak
Super User
Super User

@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])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.