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)
