SamePeriodLastYear()

Question

I need to filter on the previous value and not the current...?

When Slicing by a Year, my table shows the previous Year Value using the SAMEPERIODLASTYEAR function (MeasureSPLY).

I then want to use the Visual Level Filters, but the value shown in the table is not there, only the sliced Year value is?

Is there a way round this?

Much obliged.

ryan_mayu · Answer

Anonymous&nbsp;Have you bulded the relationship between two tables by using date column?&nbsp;If you did that, you won't see year 2018.&nbsp;LY = CALCULATE(SUM('fact'[amount]),SAMEPERIODLASTYEAR('date'[date]))&nbsp;&nbsp;&nbsp;I am curious that why you select 2019,&nbsp; you still can see 2018/1/1 in your table. The value of same period last year should in the same row as 2019/1/1.&nbsp;I think the reason why you can't see 2018 in the visual filter is because you have already choose 2019 in the year filter.&nbsp;&nbsp;

anonymous · Answer

Thanks for the response.&nbsp;The reason I could see 2018 in the table is because the Cross Filter direction Relationship from my Calendar table to Table1 was set to Single.&nbsp; If I change it to Both, 2018 is no longer displayed, but Value is not displayed in MeasureSPLY?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-lili6-msft · Answer

hi, Anonymous
First, you should know that SamePeriodLastYear is corresponding to current row context.
If there are other years in your date table, it will return SamePeriodLastYear of each of them.
For example:
current year - SamePeriodLastYear
2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 2019
2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 2018
2018&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 2017
...
And from your screen, there is no value in 2018 for [measure],
so for&nbsp;SamePeriodLastYear of 2019, there is no value of [measureSPLY]
&nbsp;
BTW: you need to learn about row context of DAX, It may help you understand it well.
&nbsp;
Best Regards,
Lin
&nbsp;
&nbsp;
&nbsp;
&nbsp;

anonymous · Answer

Many thanks for the response. I can't seem to upload photos, even though I'm signed in?? My data looks like this and has a measure value for each year: Date DimensionA DimensionB Measure1 01 January 2018 A2018 B2018 10 01 January 2019 A2019 B2019 20 01 January 2020 A2020 B2020 30 This appears to only work when Cross filter direction is set to Single. When I select 2020 from the slicer, my tables shows as expected the previous year values for 2019. This is because SAMEPERIODLASTYEAR is used in the measure MeasureSPLY. Date DimensionA DimensionB MeasureSPLY 01/01/2019 00:00 A2019 B2019 20 My question is, if I want to filter this further using Visual Filters, is there anyway to see the previous Year values shown in the table? Since the only values available in the Visual Filter are the 2020 values? Hope this makes sense. Many thanks

anonymous · Answer

Many thanks for the response. I can't seem to upload photos, even though I'm signed in?? My data looks like this and has a measure value for each year: Date DimensionA DimensionB Measure1 01 January 2018 A2018 B2018 10 01 January 2019 A2019 B2019 20 01 January 2020 A2020 B2020 30 This appears to only work when Cross filter direction is set to Single. When I select 2020 from the slicer, my tables shows as expected the previous year values for 2019. This is because SAMEPERIODLASTYEAR is used in the measure MeasureSPLY. Date DimensionA DimensionB MeasureSPLY 01/01/2019 00:00 A2019 B2019 20 My question is, if I want to filter this further using Visual Filters, is there anyway to see the previous Year values shown in the table? Since the only values available in the Visual Filter are the 2020 values? Hope this makes sense. Many thanks

Forum Discussion

SamePeriodLastYear() - Visual Level Filters

7 Replies

Related content

SAMEPERIODLASTYEAR with filter

Date filters for Visualization

Issue with SAMEPERIODLASTYEAR and Manual Time Intelligence

SAMEPERIODLASTYEAR with a year filter