SamePeriodLastYear() - Visual Level Filters

[Anonymous]

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]

@Anonymous

Have you bulded the relationship between two tables by using date column?

If you did that, you won't see year 2018.

LY = CALCULATE(SUM('fact'[amount]),SAMEPERIODLASTYEAR('date'[date]))

I am curious that why you select 2019,  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.

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.

[Anonymous]

Thanks for the response.

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.  If I change it to Both, 2018 is no longer displayed, but Value is not displayed in MeasureSPLY?

[v-lili6-msft]

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

2019            - 2018

2018            - 2017

...

And from your screen, there is no value in 2018 for [measure],

so for SamePeriodLastYear of 2019, there is no value of [measureSPLY]

BTW: you need to learn about row context of DAX, It may help you understand it well.

Best Regards,

Lin

[Anonymous]

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]

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]

I'll try again... This time on IE which allows me to upload photos, when Chrome and Edge don't?

Anyway,  many thanks for the response.

My data looks like this and has a measure value for each year:

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.

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 

[v-lili6-msft]

hi, @Anonymous

Since SamePeriodLastYear is a time-intelligence function, It needs a date time, and all the time dimension should from

date table. from your screenshot we could know that date, DimensionA, DimensionB is from data table. It couldn't be done

by this. DimensionA, DimensionB should be defined in date table.

In the visual, they should be "Tag attributes" not "time dimension". and when you filter current context you could see current context data. for example: when you filter 2020, you could only see data in 2020, and use SamePeriodLastYear is just retrun 2019 data for 2020.

Here are some documents for your reference:

https://support.office.com/en-us/article/context-in-dax-formulas-2728fae0-8309-45b6-9d32-1d600440a7ad

http://www.statslab-bi.co.nz/project/row-filter-context-dax/

Best Regards,
Lin