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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DavidKress
Frequent Visitor

Dynamically changing Date slicer

Hi I'm working off an SQL Analysis Server cube and have created a couple of DAX measures which calculate the difference in sales from the same day the prior year.  I have a table which shows end customers worldwide (thousands of customer) operating off a date slicer which defines the date to look at and compares against prior year.  Instead of having users select the date (and scrolling thru 4+ years of individual dates) I want the slicer to default to the current date minus 7 days.  The minus 7 days is because the data in the cube gets published on a 5 day time lag.   Is there an easy way to do this?  I've been trying my options, none working so far.  

 

Thanks

1 ACCEPTED SOLUTION

@DavidKress

 

I think you can try to use Power Query to create a new table which is containing today and 7 days before today. Then use this table value as slicer.

 

Please refer:

1. Go to Edit Queries, create a new blank query with Power Query script:

= {Date.AddDays(Date.From(DateTime.LocalNow()),-7),Date.From(DateTime.LocalNow())}

2. Change List to Table. Then you can get the dynamica today and 7 days before today.

2.PNG

 

Thanks,
Xi Jin.

 

View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @DavidKress,

 

In the visual filter add in the Date column and the select Relative date filtering then change to the option in the last 7 days should work you have also several options like next days, months weeks, ...

 

Relative_date.gif

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for the reply MFleix - I tried that.  The problem is because my visual is a table of customers, where I'm showing sales for the date and the same date the prior year, when I use relative filtering and change the option to the last 7 days, then what I get is the last 7 days of sales for every customers (and their sales for those same 7 days last year).  So I'm trying to get the slicer to automatically pick Feb 6 for today (Feb13).  Then tomorrow (Feb14) the slicer defaults to Feb 7, etc

@DavidKress

 

I think you can try to use Power Query to create a new table which is containing today and 7 days before today. Then use this table value as slicer.

 

Please refer:

1. Go to Edit Queries, create a new blank query with Power Query script:

= {Date.AddDays(Date.From(DateTime.LocalNow()),-7),Date.From(DateTime.LocalNow())}

2. Change List to Table. Then you can get the dynamica today and 7 days before today.

2.PNG

 

Thanks,
Xi Jin.

 

How are you calculating previous year sales?

 

WhenI tried on my sets of data with current vs previous I was abble to have the preivou months althoug dates were with relative slicer

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.