Forum Discussion

DavidKress's avatar
DavidKress
Frequent Visitor
8 years ago
Solved

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 cust...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

     

    Thanks,
    Xi Jin.

     

MFelix's avatar
MFelix
Super User
8 years ago

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

 

 

Regards,

MFelix

DavidKress's avatar
DavidKress
Frequent Visitor
8 years ago

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

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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.

     

    Thanks,
    Xi Jin.