Graph does get adjusted automatically to the Date Slicer

[JustinDoh1]

I am sharing my Pbix file here.

I have posted my issues several times, but has not gotten the solutions yet.

This is requirement:

Graph should reflect to the date range of the Date table.

Currently, there is no separate filter on the graph, so if I adjust the date slicer, graph changes accordingly.

But, what I am trying to accomplish is, whenever there is a daily data refresh, graph should follow whatever the date range shown on the Date slicer.

The whole date range for the graph, however, should not be limited to the default date range of the date slicer all the time (as data starts from 1/1/2021) - which means, date slicer can go any date before 7/13 (or when today's date slicer range starts).

I guess, I am not sure whether or how a filter should be applied to the graph or not (in order to create a default date range (Today()-16, Today()-1).

BTW, I have set up a relationship between this Date table and main table.

Thanks for help!

[lbendlin]

In your daily refresh process include a column that you calculate to be 1 for your desired date range and 0 outside of it.  (You can do that in your data source, in Power Query or as a calculated column in DAX).  Then add a page filter on that column.

[JustinDoh1]

@lbendlin Thank you for your help. I am trying to understand what you indicated. When you refer to 'daily refresh process', are you referring to somewhere in Settings at Dataset(s)? Or are you referring to modificaiton in Pbix file? Also, I realized that Date Slicer does not get updated to the date ranges that I created using a separate "Date" table (which populates date as "calendar ( today()-16, today()-1)" after daily data refresh. So, there is one more issue to my initial post. Thanks.

[lbendlin]

"I created using a separate "Date" table (which populates date as "calendar ( today()-16, today()-1)" after daily data refresh"

I would recommend against that. You want a Dates table that is marked as such, and you may want to consider a fixed table that comfortably covers your data date range - in case you want to apply time intelligence functions later. 

[mahoneypat]

You can add a column to your Date table with

Last2Wks = IF([Date] > TODAY()-16 && [Date] <= TODAY() -1, "Y", "N")

And then add a visual level filter where that column = "Y".

Pat

[JustinDoh1]

@mahoneypat Thank you for your help. I applied your calculated column to the visual, and I uploaded the updated Pbix file on the link. What I am trying to accomplish are two things: (1) When there is a daily data refresh, graph adjusts to the date range (here: today()-16, today()-1), which works fine. (2) But, I would also like the data displayed not limited to those date range after. So, I would like to use the date slicer to look for visual back in year 2020 (because there is a data). Thanks!

[mahoneypat]

Would it work if the user had to clear that filter on the Lastest 2 Weeks before they changed the date slicer?  If so, they could do that in the filter panel, with a button/bookmark, or a slicer on the report page.  I'm not sure there is a way around the user having to clear that filter somehow.

Pat

[JustinDoh1]

@mahoneypat Thank you so much for your help. I think I am at the point I kinda had to give up on automatic way that visual or data range would work without user manually clears the filters. I am just trying to understand why other people mentioned about using a separate date field (like this post) as well. Two people said it is the best practice. I am curious. Thanks.

[mahoneypat]

I looked at the post you linked, and they suggested a separate date table, which is what I assumed you had (and provided that expression to make a colum on it to make filtering to the last 14 days easier).  

To get the 14-day range, that filter needs to be set somehow (and could filter both the slicer and the visual), and to go outside of that range the user would need to clear it somehow.  The easiest way would be with a button associated with a bookmark.

Pat

[JustinDoh1]

@mahoneypat 

Thank you for your feedback.

I think using a fixed "last-14 days column" (by creating a calculated column) works on the visual only because it has an option to clear the filter.

But, when I tried with the fxied 14 column on the Date Range and published it, it would not give me an option to clear the filter. So, I had to use "Ralteve date" filter.

Either way, I realized that user has to manually clear the both filters (for visual and and for datea range) to see the data out of last 14 days. 

Please see the bottom images for description:

This is Pbix file using only Relative date on the Date Range filter.

This is Pbix file using "fixed last 14 (or 16) days calculated column" on the Date Range filter.

At this point, my initial issue with Visual filter is not relative anymore.

I am just curious why "clear filter" would not work when I add the calculated column on the Date Range.

Thanks.