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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
jn123
Frequent Visitor

Date Slicer and Compare Date Slicer

Hello, 

 

I currently have two date tables. One is .Date and the other is .Compare Date. Each are created by the function: 

CALENDAR(DATE(2022,01,01),DATE(2025,12,31))
 
I currently have the .Date table being filtered by a relative date slicer (this month, next month, this year, next year, etc). 
 
I would like to create a slicer that when "last month" (1/1/24 - 1/31/24) is selected on the slicer that is filtering the .Date table, I can select:
 
  • "Previous Period" that would filter the .Compare Date table to 12/1/23 - 12/31/23
  • "Previous Year" that would filter the .Compare Date table to 1/1/23 - 1/31/24
  • "None" that would return blank
 
I have created the following DAX measures to get the selected dates I need, but I can't quite figure out how to put this into a slicer or get this to filter the .Compare Date table instead of the .Date table:
 

Previous Period MAX = (MAX('.Date'[Date]) - 1) - [Selected Dates]
Previous Period MIN = (MIN('.Date'[Date]) - 1) - [Selected Dates]

 

Previous Year MAX = (MAX('.Date'[Date]) -365)
Previous Year MIN = (MIN('.Date'[Date]) -365)

 

Selected Dates = DATEDIFF(MIN('.Date'[Date]), MAX('.Date'[Date]), DAY)

 
 
Any advice would be appreciated!!
 
Thanks,
4 REPLIES 4
Daniel29195
Super User
Super User

@jn123 

from what i understand, and correct me if im wrong,  you need to have a slicer with the following values : 

Daniel29195_0-1708670641490.png

 

and base on the selection , it will change the measure in the visual respecting the selecteed value . 

if this is what youu want , 

you can use field paramters feature to achieve it . 

 

 

let me know if this helps . 

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

 

 

sayaliredij
Super User
Super User

HI @jn123 ,

 

If i understand correctly, i would still believe that if using measure and using proper DAX calculation which considers previous period, values from compare table can be calculated. I dont see use case for second slicer. if you do not want to allow user to select anything from the sliver 

But neverthless if you want to have possibility to add it on slicer, we can check with experts in the community

@amitchandak 

@Greg_Deckler 

@Daniel29195 

 

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




sayaliredij
Super User
Super User

Hi @jn123 ,

 

Unfortunately i am not aware how to create dynamic slicer for compare dates.

I'm having difficulty understanding the specific purpose of providing a Compare Date slicer to the user.

I was wondering if it would be possible to achieve the desired outcome by creating direct measures that calculate for the previous period or year?

 

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@sayaliredij The goal is for the end user to be able to see for example, total sales for "this" month (filtered by the .Date table) and then be able to see the percent difference of the sales "this month" vs the "previous month" or the same period in the previous year. The percent difference calculation is based on the dates in the .Compare table so I need to be able to filter the .Compare dates based on what is selected on the .Date table. 

 

Thanks,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.