Date Add Dax and Page level filters issue

[jawilson808]

I have a matrix in which I am displaying the current year values by day and the value for same time last year. Without using page level filters (or slicers), the correct value are given for same time last year. However, once I apply a page level filter for year, month or day, the value for same time last year doesnt return. Instead it returns a value for 2022

My calculated measures are as follows:
Current Year Value=sum(revenue)

Same time last year value =calculate([Current Year value],dateadd(date,-364,day))

Adding to matrix without page date filter applied returns

Date		Current Year		Same Time Last year
1/3/2020		100		105
...
1/1/2021		106		100
1/2/2021		107		98
Total		sum()		sum()
...

Same data used and page filter on date (is in this year) returns

Date		Current Year		Same Time Last year
1/1/2021		106
1/2/2021		107
...
12/30/2022				106
12/31/2022				107
Total		sum()		sum()

Any assistance would be greatly appreciated.

[Anonymous]

@jawilson808 

This is the correct behaviour. If you filter data at the page level, you filter all data and the data outside of the filter DOES NOT EXIST from the point of view of your page. You can't work around it.

[jawilson808]

Thank you @Anonymous for your reply. I think the only workaround is to build my Same time last year in my sql database.

[v-kkf-msft · Accepted solution]

Hi @jawilson808 ,

I use the following measure, then select 2021 in the page-level filter, and it output the correct result. 

Same time last year value = 
calculate(
    SUM('Table'[revenue]),
    ALL('Table'),
    dateadd('Table'[Date],-1,YEAR)
)

Could you please share your relationship between tables? Which table field are you using in the page-level filter? Could you please share your PBIX file, which will help us solve the problem faster.

Best Regards,
Winniz

[v-kkf-msft]

Hi @jawilson808 ,

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz