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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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