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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.