Forum Discussion

cj_oat's avatar
cj_oat
Helper I
1 year ago
Solved

Question regarding SAMEPERIODLASTYEAR Logic

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]))

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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 )
    )

     

2 Replies

  • shivamdubey's avatar
    shivamdubey
    New Member
    1 year ago
    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
        )
     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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 )
    )