SAMEPERIODLASTYEAR for measure with page slicer

[Jebilaya]

I'm struggling with same period last year where the page has a slicer on it, seen various articles saying this might be an issue but none of their solutions have helped.

I have a simple measure :

VolAll = countrows('Flexipod Manifest Outcome')

And I have a date table where the delivery date from flexipod manifest outcome has a relationship

But when I try and calculate for SAMEPERIODLASTYEAR I get an incorrect result.  It should produce 9,806 but actually produces 9,511

LYVolAll =

Calculate(

[VolAll],

SAMEPERIODLASTYEAR(Dates[Date]),all('Flexipod Manifest Outcome'))

[Jebilaya]

I can see this working, apart from the Week 53 issue which occurs every few years.  I suppose I could just default any week 53's that happen to use week 52 dates.

I'll try this.

[Jebilaya]

@johnt75  I tried that, no difference. Still returns 9,511

[johnt75]

Use Performance Analyzer to get the DAX code for your table visual and paste it into DAX Studio. The key bit you're after is the filter table for the dates, it will probably be something like

VAR __DS0Filter = TREATAS( { ( 2022, 3 ) }, 'Date'[Year], 'Date'[Month])

Change the EVALUATE statement at the bottom to

EVALUATE
CALCULATETABLE( SAMEPERIODLASTYEAR('Date'[Date]), __DS0Filter )

and that should show you if the dates match what you are expecting to see

[Jebilaya]

@johnt75 thanks for your input, I haven't got DAX studio but I can see there is a statement in the evaluate about cost / delivery column which has nothing to do with any filters within this page.

DEFINE VAR __DS0FilterTable =

  TREATAS(

    {("FY22", "Q3", "7", 27)},

    'Dates'[FY],

    'Dates'[Quarter],

    'Dates'[Period],

    'Dates'[Week]

  )

EVALUATE

  SUMMARIZECOLUMNS(__DS0FilterTable, "LYVolAll", IGNORE('Cost / Delivery'[LYVolAll]))

[johnt75]

I think 'Cost / Delivery' is just the table that the LYVolAll measure happens to be stored on.

DAX Studio is free to use, so if you are able to install it I'd recommend it, its very helpful in debugging situations like this.

If you can't install it then you could create a table in your model like

Dummy Table =
VAR __DS0FilterTable =
    TREATAS (
        { ( "FY22", "Q3", "7", 27 ) },
        'Dates'[FY],
        'Dates'[Quarter],
        'Dates'[Period],
        'Dates'[Week]
    )
RETURN
    CALCULATETABLE ( SAMEPERIODLASTYEAR ( 'Dates'[Date] ), __DS0FilterTable )

You can then examine that in data view and see if it has the dates you are expecting

[Jebilaya]

@johnt75 Thanks, this has identified the problem, i'm just not sure how to fix it.

It's of course returning the exact dates i.e. 3rd to 9th July for 2021 and 2022.  However what I actually want is it to look at the corresponding week for last year which was 28th June to 3rd July.  I.e. compare week 27 from this year with week 27 from last year.

However, I have a dynamic slicer allowing user to select week, Period to date, quarter to date etc. so I want my sameperiodlastyear to replicate those selections.

I suspect SAMEPERIODLASTYEAR is not exactly what I want to use here

[johnt75 · Accepted solution]

Yes, I don't think SAMEPERIODLASTYEAR will do what you want it to.

One possible solution would be to add another column to your Date table, Same Date Last Year, and you could put all your logic into this column - e.g. same week number and same day of week in last year. You could then use this in your calculations like

Last Year Measure = CALCULATE( [This Year Measure], TREATAS( VALUES( 'Date'[Same Date Last Year]), 'Date'[Date]) )

[Jebilaya]

@johnt75 So I now have a date last year within my date table and a LYvolall measure with dax:

LYVolAll =

Calculate(

[VolAll],

TREATAS(values(Dates[DateLY]),Dates[Date]))

However this is still returning a blank value

[johnt75]

Try

LYVolAll =
CALCULATE (
    [VolAll],
    TREATAS ( VALUES ( Dates[DateLY] ), Dates[Date] ),
    REMOVEFILTERS ( Dates )
)

I think the TREATAS is being added to the existing filter context on Dates[Date] rather than replacing it and so the result of both filters being applied is an empty set, hence a blank result.

[Jebilaya]

@johnt75 Firstly thanks so much for your patience!

Still have a blank, I wonder if it's to do with the way the slicer is formed which references another calculated table based on the desired selection

[Jebilaya]

@johnt75 just tried it on a blank sheet with a standard date filter rather than the period, quarter etc. slicer and the measure works fine so it's definitely something to do with that so i'll try and work on that.

Thanks for the help though!

[johnt75]

Yes, it could be. Try adding a REMOVEFILTERS on the table which the slicer is coming from

[johnt75]

Try removing the ",all('Flexipod Manifest Outcome')"