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 I

## Dynamic SAMEPERIODLASTYEAR

Hi PBI users,

I'm looking to create a dynamic SAMEPERIODLASTYEAR calculation. I have a Matrix visual where you can drill down between Year, YYQQ and YYMMM. After a user drills down and selects the appropriate timeframe, I would like the measure below to be dynamic enough to compare against the same period of the previous year. So if I select year 2022, I'd expect a the value from 2021. If I drill down and select 23Q1, I'd expect a value from 22Q1. Hope this makes sense. This seems pretty straightforward but can't seem to get it down. I'm also trying to avoid a lengthy IF ELSE statement but will take any solution at this point.

M-Volume_SPPY =
var _year_filter = CALCULATE(ISFILTERED('Sort-Year'[S-Year]),ALLSELECTED('Sort-Year'[S-Year]))
var _yyqq_filter = CALCULATE(ISFILTERED('Sort-YYQQ'[S-YYQQ]),ALLSELECTED('Sort-YYQQ'[S-YYQQ]))
var _yymmm_filter = CALCULATE(ISFILTERED('Sort-YYMMM'[S-YYMMM]),ALLSELECTED('Sort-YYMMM'[S-YYMMM]))
var _sppy = CALCULATE(COUNT('Table'[Account]),SAMEPERIODLASTYEAR('Table-Calendar'[Date]))
return
SWITCH(TRUE(),
_year_filter = FALSE ,"-",_sppy,
_yyqq_filter = FALSE ,"-",_sppy,
_yymmm_filter = FALSE ,"-",_sppy,
BLANK())

__________

20230301 Update

I've updated the SWITCH statement to the follow and it seems to work. Is there anything else I should do? Is there an option to not have to explicitly call out [S-Year], [S-YYQQ], [S-YYMMM]?

M-Volume_SPPY =
var _year_filter = CALCULATE(ISFILTERED('Sort-Year'[S-Year]),ALLSELECTED('Sort-Year'[S-Year]))
var _yyqq_filter = CALCULATE(ISFILTERED('Sort-YYQQ'[S-YYQQ]),ALLSELECTED('Sort-YYQQ'[S-YYQQ]))
var _yymmm_filter = CALCULATE(ISFILTERED('Sort-YYMMM'[S-YYMMM]),ALLSELECTED('Sort-YYMMM'[S-YYMMM]))
var _sppy = CALCULATE(COUNT('Table'[Account]),SAMEPERIODLASTYEAR('Table-Calendar'[Date]))
return
SWITCH(TRUE(),
_year_filter = TRUE && _yyqq_filter = FALSE && _yymmm_filter = FALSE, _sppy,
_year_filter = FALSE && _yyqq_filter = TRUE && _yymmm_filter = FALSE, _sppy,
_year_filter = FALSE && _yyqq_filter = FALSE && _yymmm_filter = TRUE, _sppy,
0)
1 ACCEPTED SOLUTION
Super User

SAMEPERIODLASTYEAR is syntax sugar for DATEADD. As long as you use a suitable date column (contiguous and covering) it doesn't matter what period you are looking at.

Super User

SAMEPERIODLASTYEAR is syntax sugar for DATEADD. As long as you use a suitable date column (contiguous and covering) it doesn't matter what period you are looking at.

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