Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Setting a default value in a slicer

good morning, I currently have a report that contains 2 slicer, one associated with years and another with months, I want to leave pordefecto every time it is opened and report are selected automatic...
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Syndicate_Admin ,

     

    Maybe you can try the function ISFILTERED().

    IF no selection, then get MONTH of TODAY() and use CALCULATE() to sum the value of this month.

    You can refer the following expression:

    Total =
VAR _today =
    TODAY()
VAR _Defult =
    CALCULATE(
        SUM( 'DATE'[Value] ),
        FILTER( ALL( 'DATE' ), MONTH( [DATE] ) = MONTH( TODAY() ) )
    )
RETURN
    IF( ISFILTERED( 'DATE'[Month] ), SUM( 'DATE'[Value] ), _Defult )

    The result:

     

    I put the pbix file in the end you can refer.


    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

hello, what I want is that when the report is opened the slicers that are showing date and year are default to the current feha and update the values of the report

the panel is this

mancemo_0-1637762681687.png

and that when it opens automatically, year and month are selected to update the other indicators

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
4 years ago

mancemo 

 

It's not possible to set the default of your current slicer layout to current month. You could create a calculated column in Power Query that for example for this month of November would have the values: 

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Current

Dec

 

And do the same for the Year. That way you could set the default value of both slicers to 'Current' and then the use can choose a different month if they desire. 

 

To do this, click Transform Data to open Power Query.

 

Click Add Column tab in the ribbon, then click Custom Column and use this code: 

 

if [Month name] = Date.MonthName(DateTime.LocalNow()) then "Current" else [Month name]

 

 

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    hello thanks, I tell you a little that the date table I get from the following query

    DATE = CALENDAR (DATE (2020, 1, 1), DATE (2021, 12, 31))
    try so
    my = IF [DATE[Date].MONTH] = Date.MonthName(DateTime.LocalNow()) then "Current" else [[DATE[Date].MONTH])
    and it makes me an error
    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      4 years ago

      mancemo 

       

      Sorry for not being clear - the formula I gave is M (not DAX) so you need to click Transform Data to add this in Power Query. Since your Date table uses DAX, you will need to create the Date table in Power Query to use this method: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

       

      Alternative option is to use DAX to create the column, but it's less efficient. 

      New Column = 

      VAR _CurrentMonth = MONTH(TODAY())

      RETURN
      IF( _CurrentMonth = Date[Month], "Current", Date[Month])