How to dynamic aggregate sum sales and same period last year

[Shineccx007]

Hi Teams,

I have created two calendar tables to aggregate the sales total.

Date period is to apply the date type slicer on the filter.

Value entries date is from Value entries table "posting date"

The relationship between value entries calendar table and date period table is "one to many" linked by the date.

I am able to get what I want in the slicer for the date selected. But I am not able to get the calculate 1 year before the slicer selected. Same period last year is showing all the data in the past, which is not I want.

For example,if I select slicer YTD, SPLY sales should sum the sales between 01/01/2023~10/04/2023

If I select past 6 months, the SPLY sales should sum the sales 04/01/2023~9/30/2023

if I select past12 months,the SPLY sales should sum the sales between 10/1/2022~9/30/2023

I have tried several ways below,none of them works:

1. To switch the date under SPLY to sameperiodlastyear(date period[date]) but show error on the DAX and no result is showing there.

2. To mitigate by the value entiees calenda date,but seems data is not showing what I want either.
3. Create one more column LastYearSamePeriod under date period table, DAX show error. 

Can anyone help?

[lbendlin · Accepted solution]

Add a column to your calendar table "IsPastPY"  that is calculated on each refresh (so ideally daily).  Add that column as another filter when you use SAMEPRIODLASTYEAR.

[Shineccx007]

Hi Ibendlin,

I added a column "last year same period "under the date period table and aggregate by this column. I got my answer.

Thank you.