cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft mebab
Microsoft

Default value for filters

I have a report with a date filter in it. Is it possible to give default values to them (preferably pogrammatically) such that every time the report loads, the report is shown for the default values, yet can be run on different date values when changed in the filter provided?
Programmatic change is preferred since the report should be shown by default for the last two weeks (for ex.).
Any help?

Thanks!

9 REPLIES 9
Super User
Super User

Re: Default value for filters

You can definitely set the filters for the report for defaults, just adjust the values in the Filters area and save the report. However, if someone updates the filters and saves the report, it will change the default. However, if they change the filter and do not save, you will be fine.

 

Programmatic is not possible right now I believe, you can put a formula like "TODAY()" in the filter and when you apply it, it changes to today's date, but I do not believe that it persists as a formula right now or that you can put in something like "TODAY()-14" for example.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

konstantinos Senior Member
Senior Member

Re: Default value for filters

@mebabTo extend @Greg_Deckler suggestion you can add a calculated column in your Date table sort of like this 

 

1. Switch ( TRUE ; AND( DateTable[Date] =< Today();DateTable[Date] > Today() - 15 ) ; " Last 2 Weeks" ; ........etc..

 

then either you create more periods or leave the date filters..to finish the formula..

 

After you add this column as filter and select by default the "Last 2 Weeks"..Everytime it refreshes the filter will contains the latest 14 days..

 

2. or with disconnected slicers...a simplified version of http://www.powerpivotpro.com/2013/06/simplifying-time-calculations-and-the-user-experience-using-dis...

Konstantinos Ioannou
Microsoft mebab
Microsoft

Re: Default value for filters

Hi, thank you for the replies.

My issue is that, I want the report to be run and ready for the up-to-date data but when the user opens it, the filters should contain a default value of start and end dates specifying last two weeks. But the user gets the privilege to change the date filter to any past date and that way the report gets updated with the new datetime values chosen.

Is there any way to support this?

Super User
Super User

Re: Default value for filters

Had a thought on this, alas it did not work. Created two measures, TODAY() and TODAY()-14 as their formulas. That's great, but you cannot set the filters to a measure value apparently. Thought was that you could go to Advanced Filtering and set "is on or after" the second measure and "is on or before" first measure.

 

No dice.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

konstantinos Senior Member
Senior Member

Re: Default value for filters

Where do you want the date filters to be selected from user..You cannot actually refresh the data based on the selection afterwards since all data are loaded and cached ( except live data sources)..There are 2 options or as a slicer or in the filters pane..

If you need to slicers/filters, one with start date and one with two dates like ( from excel but cann do the same in PowerBI Desktop - not in browser )..let me know and I will explain more..

 

 

5.png

Konstantinos Ioannou
Microsoft mebab
Microsoft

Re: Default value for filters

@konstantinos, could you please elaborate? I couldn't properly understand the solution you gave.

 

Thanks!

Highlighted
celcius Frequent Visitor
Frequent Visitor

Re: Default value for filters

If yu have a date table set a calculated column as

Days_From_Today = IF([DateId] <= TODAY(), 0 - DATEDIFF([DateId], TODAY(), DAY),DATEDIFF(TODAY(), [DateId], DAY))
where [DateId] is the date column in your date table. This will allow you to set a filter on Days_From_Today of Between -12 and 0.

You can apply this column to your data if you don't have a date table.

celcius Frequent Visitor
Frequent Visitor

Re: Default value for filters

Create a calculated column:

Days_From_Today = IF('Date'[DateId] <= TODAY(), 0 - DATEDIFF([DateId], TODAY(), DAY),DATEDIFF(TODAY(), [DateId], DAY))

 

then use it to set your filter as "between -12 and 0". this will then always be the last two weeks.

Anonymous
Not applicable

Re: Default value for filters

Did you ever come up with a resolution for this issue? I too would like my published web report date slicer to default to a date range upon loading, but then still provide users the ability to specify a different range once they're working in the report.

 

Thanks!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,607)