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
seanguerrero
Helper I
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())
 
Thanks in advance!
 
__________
 
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
lbendlin
Super User
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.

View solution in original post

1 REPLY 1
lbendlin
Super User
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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors