Only show Last X days based on date slicer selection

[joew98]

Hi,

I have a filter to show data from (for example) the last 7 days. If I apply a date slicer to allow the user to go back and select a date 6 months ago (e.g. 11th January), how do I ensure that the relative date filter still applies so that the data only displays the last 7 days before 11th January?

[joew98]

@v-yueyunzh-msft Hi thanks for the help. This solution works with table visuals but do you know how to get it to work for card visuals? When I apply the measure to 'Filters on this visual' for my card visuals, it does not allow me to select it. Do you know a workaround?

[v-yueyunzh-msft]

Hi , @joew98 

Thanks for your quick response ! If you want to put this in the card visual , you need to realize it in the dax .Like this:

Measure 2 = var _days  = MAX('Last N Days'[Last N Days])
var _selected_date = MAX('Date'[Date])
var t2 = FILTER('Fact Table' ,'Fact Table'[Date] <= _selected_date && 'Fact Table'[Date]>_selected_date -_days)
return
sumx(t2,[Value])

Then we can get it as the same :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[joew98]

Hi @v-yueyunzh-msft ,

I can't seem to get that to work for me, would you be able to help? I can add the measure to the card visual but cannot select it to change from 'is less than'. 

[v-yueyunzh-msft]

Hi , @joew98 

Thanks for your quick response! You can check if you may create any relationship :

And you can also download the pbix file below.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi, @joew98 

According to your description, you want to create two slicers in your visual . One is the Last N days and another is the selected Date.

Here are the steps you can refer to :
(1)This is my test data and we do not need to create any relationship between tables.

And The Date in my side is created by this:

Date = CALENDAR(FIRSTDATE('Fact Table'[Date]) , LASTDATE('Fact Table'[Date]) )

(2)Then we can create  a Numeric range parameter as a "Last N days" slicer.

(3)Then we can create a measure like this:

Measure = var _days  = MAX('Last N Days'[Last N Days])

var _selected_date = MAX('Date'[Date])

return

IF( MAX('Fact Table'[Date]) <= _selected_date && MAX('Fact Table'[Date])>_selected_date -_days ,1,-1)

Then we can put the measure on the "Filter on this visual" and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly