cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## SAMEPERIODLASTYEAR for measure with page slicer

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

1 ACCEPTED SOLUTION
Super User

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]) )``
13 REPLIES 13
Helper III

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.

Helper III

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

Super User

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

Helper III

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

Super User

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

Helper III

@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

Super User

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]) )``
Helper III

@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
Super User

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.

Helper III

@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

Helper III

@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!

Super User

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

Super User

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

Announcements

#### 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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors