cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jawilson808
Helper I
Helper I

Date Add Dax and Page level filters issue

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.

1 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)
)

image.pngimage.png

 

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@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.

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

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

image.pngimage.png

 

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors