Set date range slicer to min and max to showing latest one month of data by default

[ppatel91]

Hello there,

I am very new with DAX and I have trouble creating measure that shows latest one month of date range and data accordingly. So I suppose today's date is 4/5/2021 then I want my date slicer to show the date range between 4/3/2021 to 4/5/2021 by default and my widgets should reflect the latest data according to date. I have tried 'TOP N' and 'Relative date' filters but my date is as is, nothing is changing to date range. Also, I don't have any 'Date' or 'Calender' table and even I don't know how to create/use them. I am using date column which is associated with my original table. Note: we are using 'Direct Query' to database. Please let me know how can I accomplish this. I have tried different measures of power BI community but none of them worked.

Thanks in advance

[MFelix]

Hi @ppatel91 ,

You refer that you want to show by default in the slicer the values of the last month, do you mean that the user can then select other values?

If you want to always select the previous X month you must use the relative filter on the filter pane, this allows to select the period and if today is included or not and this should be added to the filter of the page or report in order to affect all the visualizations:

Has you can see above all the visualization only show information in the last month and when the date changes it will return only the last month,

However this will be fixed to the last month if you want to change this on the filter pane you need to create a different selection.

I return to my question do you want the users to select other period if they need? is it based on the last x time or is it variable?

You can create a measure that give you the result based on a button select.

[ppatel91]

Hello @MFelix ,

Thank you for clarification! Yes, I want my default date and data from last one month to current date. However, my date slicer should allow user to change date range according to their choice.

[MFelix]

Hi @ppatel91 ,

You can do this using a bookmark, so you could have a default bookmark that would pick up the last month and then making use of a button you would have one that would open your report to your latest dates.

You need to add the filter with the relative time date and create a bookmark, then take that relative date and add another bookmark,

Now just need to add two buttons one for each option:

[ppatel91]

@MFelix 

Your solution is working fine. But the problem is when you click on last month dates and then if you want to select the date range, it will not reflect changes on other visuals because it using relative date. Then customer has to choose all dates in order to select date range which will be so confusing for our customers. That's why I was looking for a solution which can directly show the default date range and data from last one month and then customer can change the date range according to their choice. but my date range should be showing latest one month range so that it can help us with the performance point of veiw as well. Could you provide me a solution according to my above expenctations?

Thank you so much for your detailed exaplaination. As a completely new power bi user it helping a lot and definitely I am learning a lot from your posts.

[MFelix]

Hi @ppatel91 ,

Where are you placing the relative filter?

You need to place the relative filters on the page level so that can influence all the visualizations.

That will allow for you to then change the slicer and thr visualizations accordingly.

[ppatel91]

@MFelix , I am using at page level. 

[MFelix · Accepted solution]

Hi @ppatel91 ,

I'm missing something in my test has you can see below I can use the slicer within the relative slicer and only return the last days I select in the slicer:

Has you can see my visuals are impacte by the slicer selection and when I click the all dates the slicer clears all the other dates.

Not really understanding the error, do you want to change the slicer for all dates and keep the previous selection of the slicer is that it?

[ppatel91]

@MFelix , Actually, we turned it off the slicer selection and user can select by showing in the below picture. In that case, user can select any date but whenever user open this report named 'search report' they should see one of data and date by default. then after they can make their own selection. Also, when I applied your suggested filter and bookmarked it, my last date(see red arrow in the picture) is not reacting at all. Why is this?

[MFelix]

Hi @ppatel91 ,

I have tested out the slicer based no the date pane and everything is working properly, only question is that if you select values outside the current month you will not get results outside.

So you need to select all dates to do the selection of other date.

[v-robertq-msft]

Hi, @ppatel91 

According to your sample picture, it seems that you have done everything right. I think you should make sure the field you placed into the slicer is the same as the field you placed into the visual filter. And when you click on the button in the Power BI desktop, you should press “Ctrl” and click to make the button work.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks

If you still have a problem, you can post your sample test pbix file (without sensitive data) so that we can help you in advance.

Thank you very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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