How to default a Date Slicer to a specific (dynamic) date range when opening a report

[SGP1]

Hello,

I have a Fact transaction table with dates and would like to default the date slicer in my Power BI report to the latest date in the fact table and the 6 days previous, so effectively whenever anyone opens the report it always defaults to the latest available 7 days transactions. My fact table is usually 3 or 4 days behind today’s date so I cannot use the Relative slicer within the last 7 days as the filter. I have many visuals in my report and would obviously like them all to default to the last weeks activity.
I would also like the user to be able to select any earlier dates within the slicer after viewing the latest 7 days activity. Does anyone know how this can be achieved? I am able to get the dates I want into Measures by using LASTDATE and LASTDATE -6, but can’t seem to be able to incorporate these into the Date Slicer as the default values whenever my report opens.
Thank you very much.

[pc2 · Accepted solution]

If you want to use it in a slicer, you have to have the values in a Calculated Column, not in a Measure.

I would add a calculated column in your FactTable and calculate the number of days, then put these values into categories, "Last 7 days", "8 - 14 days", "15-21 days", etc.   Then you can use this column as a slicer.

[amitchandak · Accepted solution]

@SGP1 , You can try like

Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -6

return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))

Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -7

return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))

In case you need to show the trend

https://www.youtube.com/watch?v=duMSovyosXE

[pc2 · Accepted solution]

If you want to use it in a slicer, you have to have the values in a Calculated Column, not in a Measure.

I would add a calculated column in your FactTable and calculate the number of days, then put these values into categories, "Last 7 days", "8 - 14 days", "15-21 days", etc.   Then you can use this column as a slicer.