Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 :
Solved! Go to 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]) )
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.
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
@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]))
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
@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
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]) )
@johnt75 So I now have a date last year within my date table and a LYvolall measure with dax:
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.
@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
@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!
Yes, it could be. Try adding a REMOVEFILTERS on the table which the slicer is coming from
Try removing the ",all('Flexipod Manifest Outcome')"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |