cancel
Showing results for
Did you mean:

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

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
Community Champion

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!

3 REPLIES 3
Community Champion

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!

Community Champion

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

Resident Rockstar

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors