Dynamically select past N days of dates & table rows based on Slicer

[dv_]

Hello, Power BI Community,

Long story short, I am trying to display a table that shows the N days of dates & table rows based on Slicer. I've spent the past two days trying various solutions presented here (some of them below) to no avail.

Link to sample file here: https://drive.google.com/file/d/1y81osptUSc9qwE7DvdR0hQbmANZvEJS6/view?usp=sharing 

The requirement is to filter and only show the past N days from the filter selection, as per the below picture. There's an additional table that cannot be changed. 

These were the posts that seemed to be closest to my issue, but I still couldn't make it work. Thanks, @PaulDBrown and @v-yueyunzh-msft for suggestions.

 @PaulDBrown: show pat 7 days from selected date - Microsoft Fabric Community

@v-yueyunzh-msft : Solved: Re: Only show Last X days based on date slicer sel... - Microsoft Fabric Community

Any suggestions / help would be hugely appreciated.

Thank you.

[kpost · Accepted solution]

 

See attached dv_solution.pbix report with a working solution.  I also downloaded your file and implemented the same strategy, essentially fixing the issue, 'Sample Report.pbix'

1) I created a copy of the fact table with no active relationship to the Date Dimension table. (You may choose to use SELECTCOLUMNS instead, only including the fields you want in the copy table, to reduce the size of your semantic model).

2) Used fields from the COPY table in the table visual.

3) Turned ON visual level interactions between the date slicer and the table in question

4) Added a visual-level filter that depends upon the selected date, and goes back 7 days

In the file dv_solution.pbix I also added an "N" filter, to allow you to go back N days, because I wasn't sure whether you wanted that hard-coded or not.

Here's the filter:

Visual Level Filter = 
VAR selected_date = selectedvalue('Calendar'[Date])

var table_Date = selectedvalue(copy_table[For Date])

return 
IF(table_Date >= selected_date - 7 && table_Date <= selected_date , 1, 0)

Hopefully this helps.

 

Mediocre Power BI advice, but it's free///

[kpost · Accepted solution]

 

See attached dv_solution.pbix report with a working solution.  I also downloaded your file and implemented the same strategy, essentially fixing the issue, 'Sample Report.pbix'

1) I created a copy of the fact table with no active relationship to the Date Dimension table. (You may choose to use SELECTCOLUMNS instead, only including the fields you want in the copy table, to reduce the size of your semantic model).

2) Used fields from the COPY table in the table visual.

3) Turned ON visual level interactions between the date slicer and the table in question

4) Added a visual-level filter that depends upon the selected date, and goes back 7 days

In the file dv_solution.pbix I also added an "N" filter, to allow you to go back N days, because I wasn't sure whether you wanted that hard-coded or not.

Here's the filter:

Visual Level Filter = 
VAR selected_date = selectedvalue('Calendar'[Date])

var table_Date = selectedvalue(copy_table[For Date])

return 
IF(table_Date >= selected_date - 7 && table_Date <= selected_date , 1, 0)

Hopefully this helps.

 

Mediocre Power BI advice, but it's free///

[dv_ · Accepted solution]

Beautiful! Thank you so much! Now I have to do this 12 more times, for my entire report. 🙂

But I can only replicate the columns I need, which will make it easier and lighter on the model.

Thanks again, @kpost .