Filter should be automatically selected based on current month

Hi,

I have a Year, Quarter and Month Hierarchy in a filter and the report data will change based on the selected filter. Now, I want to show the report based on current Month on the initial load of report.

For example, if we are in August, it should select the months till August by default. Whenever we load the report intially, it should select July & August. And on 1st september, July, August & September need to be automatically selected.

Could someone help me over this?

Thanks in advance!

v-xuding-msft

6 years ago

Hi v-srekom ,

It is not supported for slicers to be selected by default while openning reports, unfortunately. But product group is looking into this feature. Please vote up the idea: https://ideas.powerbi.com/ideas/idea/?ideaid=39ba67cf-724e-4b6c-9906-51163749f759 to get it fixed quickly. For now, we just can show default values in visuals, like below.

Workaround:

A disconnected date table:

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Measures:

Total sales = SUM('Table'[Sales])

Measure =
VAR monthtoday =
    MONTH ( TODAY () )
VAR yeartoday =
    YEAR ( TODAY () )
VAR selectedmonth =
    ALLSELECTED ( 'Date'[Date].[Month] )
VAR true_ =
    CALCULATE (
        [Total sales],
        FILTER (
            'Table',
            'Table'[Date].[Month] IN selectedmonth
                && 'Table'[Date].[Year] = MAX ( 'Date'[Date].[Year] )
        )
    )
VAR false_ =
    CALCULATE (
        [Total sales],
        FILTER (
            'Table',
            'Table'[Date].[MonthNo] = monthtoday
                && 'Table'[Date].[Year] = yeartoday
        )
    )
RETURN
    IF ( ISCROSSFILTERED ( 'Date'[Date].[Month] ), true_, false_ )

show current values by default.pbix58 KB

5 Replies

AllisonKennedy
Community Champion
6 years ago
v-srekom
Not sure I understand - do you only want Jul Aug when we're in August or should June be selected too?

Could you use a relative date slicer instead of the one in your image?

Other option is to add a filter on the slicer for DimDate[Date] and use a relative date filter on that to only show data in the last three months, and then the user can select which three months they want within that constraint.
- v-srekom
  Microsoft Employee
  6 years ago
  AllisonKennedy
  First, I'll clear your confusion: As we are using fiscal year, teh cycle is from July to June of next year.
  And just explaining the things more clearly:
  1. We have budget values for future months/quarters. So, we need to show the future months in the slicer so that client can manually select future months.
  2. Whatever the month we are in, all the months till current month need to be selected by default whenever we open the report
  - v-xuding-msft
    Community Support
    6 years ago
    Hi v-srekom ,
    
    It is not supported for slicers to be selected by default while openning reports, unfortunately. But product group is looking into this feature. Please vote up the idea: https://ideas.powerbi.com/ideas/idea/?ideaid=39ba67cf-724e-4b6c-9906-51163749f759 to get it fixed quickly. For now, we just can show default values in visuals, like below.
    
    Workaround:
    
    A disconnected date table:
    
    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
    
    Measures:
    
    Total sales = SUM('Table'[Sales])
    Measure = VAR monthtoday = MONTH ( TODAY () ) VAR yeartoday = YEAR ( TODAY () ) VAR selectedmonth = ALLSELECTED ( 'Date'[Date].[Month] ) VAR true_ = CALCULATE ( [Total sales], FILTER ( 'Table', 'Table'[Date].[Month] IN selectedmonth && 'Table'[Date].[Year] = MAX ( 'Date'[Date].[Year] ) ) ) VAR false_ = CALCULATE ( [Total sales], FILTER ( 'Table', 'Table'[Date].[MonthNo] = monthtoday && 'Table'[Date].[Year] = yeartoday ) ) RETURN IF ( ISCROSSFILTERED ( 'Date'[Date].[Month] ), true_, false_ )
    
    show current values by default.pbix58 KB
amitchandak
Super User
6 years ago
v-srekom , Slicer can not be initialized on functions /rules, as of now.

You can create a column like this in your date table and select this qtr

qtr Type = Switch( True(),
[start qtr]<=Today() && [end qtr]>=Today(),"This Qtr" ,
[qtr Name]
)