Question regarding SAMEPERIODLASTYEAR Logic

[cj_oat]

Hi guys,

I'm trying to use SAMEPERIODLASTYEAR to see selected week based on slicer for This Year (TY) and Last Year (LY), however, when I select the week, it seems to reflect in TY column only but not in LY column as screenshot below (example here is I selected week 4)

I am trying to ensure that LY Week Selected also shows the number that is filtered in "Week" Slicer (or Max number of week if multiple weeks are selected in Slicer)

TY Week Selected = 
if(ISFILTERED('Calendar Table'[Week Number]),MAX('Calendar Table'[Week Number]),MAX('Sales Table'[Latest Week Number]))

LY Week Selected = CALCULATE([Dummy Value],SAMEPERIODLASTYEAR('Calendar Table'[Date]))

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but when searching for last year's same week number value, please try something like below.

LY selected week sales: = 
VAR _currentyear =
    MAX ( 'calendar'[Year] )
VAR _lastyear = _currentyear - 1
VAR _selectedweeknumber =
    MAX ( 'calendar'[Week number] )
VAR _lastyearperiod =
    FILTER (
        ALL ( 'calendar' ),
        'calendar'[Year] = _lastyear
            && 'calendar'[Week number] = _selectedweeknumber
    )
RETURN
    IF (
        HASONEVALUE ( 'calendar'[Year] ),
        CALCULATE ( [This year selected week sales:], _lastyearperiod )
    )

[shivamdubey]

Below measure will give you selected week from slicer and if not selected then you can return the max value of the table. In your case it is sales table

Selected_Week = IF(ISFILTERED('Table'[Week Number]),SELECTEDVALUE('Table'[Week Number]),MAX('Table'[Week Number]))

Below measure will help with the LY week. 

LY week =

VAR LY =MAX ( 'Table'[Year] ) - 1

VAR Selectedweek = MAX ( 'Table'[Week Number] )

RETURN

CALCULATE (

        WEEKNUM(MAX ( 'Table'[Date] )),

        'Table'[Year] = LY, 'Table'[Week Number] = Selectedweek

    )