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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.