cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrmh Regular Visitor
Regular Visitor

set a default value on a slicer

Hello,

 

From reading im pretty sure the answer to this is "no".

But some of the questions re this are dated, so Im hoping we have moved forward since.

 

I have a slicer for Fiscal period.

Capture1.JPG

 

It is driven of a DimDate table/dimension we have built, that as well as a heap of various ways of manipulating and displaying dates, I have also added a heap of boolean (bit) fields to tell me things like is this month, is this week, is last month, is this financial year etc.

 

We use this slicer to show our costs vs invoicing (and target monthly invoicing). The user of the report can select any month in the current or previous financial year (this is all working)
Capture2.JPG

 

Simply, all we need to be able to do, is have the current fiscal month (based off our datedim, IsCurrentMonth=1), be the default selection when the user is first given the report.

 

Further, I am aware of visual, page, and report filters. We are not interested in these, we use these in other reports for "Month to Date" style reporting. This report we want the user to be able to enquire at their leisure on any period of interest, only defaulting to the current period when the report is first loaded.

 

Hoping the answer has progressed beyond "you cant do this".

 

Any thoughts or comments welcome.

Thanks,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: set a default value on a slicer

Hi @andrmh,

 

In my test, I created a DimDate table which has no relationship with actual data table. In DimDate table, create a Month column which will be added to slicer later.

1.PNG

 

Then, apply filter to measures. Suppose the original measure is like:

Invoiced to date = SUM('Fiscal Sales'[Amount])

 

Please adjust it to:

Invoiced to date =
IF (
    HASONEFILTER ( DimDate[Month] ),
    CALCULATE (
        SUM ( 'Fiscal Sales'[Amount] ),
        FILTER (
            'Fiscal Sales',
            'Fiscal Sales'[Date].[MonthNo] = MAX ( DimDate[Month] )
        )
    ),
    CALCULATE (
        SUM ( 'Fiscal Sales'[Amount] ),
        FILTER ( 'Fiscal Sales', 'Fiscal Sales'[Date].[MonthNo] = MONTH ( TODAY () ) )
    )
)

2.PNG3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: set a default value on a slicer

Hi @andrmh,

 

In my test, I created a DimDate table which has no relationship with actual data table. In DimDate table, create a Month column which will be added to slicer later.

1.PNG

 

Then, apply filter to measures. Suppose the original measure is like:

Invoiced to date = SUM('Fiscal Sales'[Amount])

 

Please adjust it to:

Invoiced to date =
IF (
    HASONEFILTER ( DimDate[Month] ),
    CALCULATE (
        SUM ( 'Fiscal Sales'[Amount] ),
        FILTER (
            'Fiscal Sales',
            'Fiscal Sales'[Date].[MonthNo] = MAX ( DimDate[Month] )
        )
    ),
    CALCULATE (
        SUM ( 'Fiscal Sales'[Amount] ),
        FILTER ( 'Fiscal Sales', 'Fiscal Sales'[Date].[MonthNo] = MONTH ( TODAY () ) )
    )
)

2.PNG3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.