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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
peterhui50
Helper III
Helper III

Optimize a measure?

Is there a way to optimize this measure?

 

Items_Sold__Rolling_12_Months__Last_Year =
CALCULATE ([Items Sold],
SAMEPERIODLASTYEAR (
DATESINPERIOD ( DATE_TABLE[Date], LASTDATE ( DATE_TABLE[Date] ), -12, MONTH )) )
 
A bit of explanation --
DATESINPERIOD measure will bring me to Dec 31 2021 because my last date is Dec 31 2021. The SAME PERIOD LAST YEAR then flips it to Dec 31 2020. Which is what I want, I want to get the items sold for Dec 2020.
 
Is there a better way? seems redundant. I can probably just do DATESBETWEEN() but I don't want to hard code it. I don't want to do
CALCULATE([Items Sold], DATESBETWEEN(DATE_TABLE[Date], DATE(2020,1,1) , DATE(2020,12,31))
1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
Community Champion

 @peterhui50 

 

I looked at this before @Vera_33 posted a reply...

 

Try this

ItemsSoldRolling12MonthsLastYear =
CALCULATE(
	[ItemsSold],
	PREVIOUSYEAR(DATE_TABLE[Date]))
)

This requires you have a date table and that it's marked appropriately.  Hope this helps!

View solution in original post

3 REPLIES 3
littlemojopuppy
Community Champion
Community Champion

 @peterhui50 

 

I looked at this before @Vera_33 posted a reply...

 

Try this

ItemsSoldRolling12MonthsLastYear =
CALCULATE(
	[ItemsSold],
	PREVIOUSYEAR(DATE_TABLE[Date]))
)

This requires you have a date table and that it's marked appropriately.  Hope this helps!

@peterhui50 how are @Vera_33 and my solutions looking?  😉

Vera_33
Resident Rockstar
Resident Rockstar

Hi @peterhui50 

 

How about filtering the Year?

CALCULATE ([Items Sold],FILTER(DATE_TABLE, DATE_TABLE[Year]=YEAR(LASTDATE(DATE_TABLE[Date]))-1))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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