Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.