Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power Slicer Custom Visual

I have a month year column as shown in the picture. The most recent month year would always be the end of month of the previous month.

 

For example, we are in March 2019 now so the most recent month year in my slicer would be Feb 2019.

 

I heard about the new Power BI custom visual called Power Slicer where we can set a default value for the slicer by giving in a simple Java code.

  Any ideas on what would go to the default value for the default to always be the most recent month year in the slicer automatically? If i set this, then next month (April 2019) when i refresh the report, the slicer would automatically change to March 2019.

 

Thank you and I appreciate the help!

Need Help
Show and Tell

10 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    We can create a calculated column to work on it.

     

    Column = 
VAR _yearmonthtoday =
    YEAR ( TODAY () ) * 100
        + MONTH ( TODAY () )
VAR _yearmonthPrevious =
    YEAR ( EOMONTH ( TODAY (), -1 ) ) * 100
        + MONTH ( EOMONTH ( TODAY (), -1 ) )
VAR _yearmonth =
    YEAR ( 'CALENDAR'[Date] ) * 100
        + MONTH ( 'CALENDAR'[Date] )
RETURN
    IF (
        _yearmonth = _yearmonthtoday,
        BLANK (),
        IF (
            _yearmonthPrevious = _yearmonth,
            "the recent YEAR MONTH",
            YEAR ( 'CALENDAR'[Date] ) & "-"
                & FORMAT ( 'CALENDAR'[Date], "MMM" )
        )
    )

     

    Regards,

    Frank

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-frfei-msft 

       

      Hey, really appreciate the help! Can you please explain how this would work in a slicer? (How would the slicer change it's selection to March 2019 next month automatically?)

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Anonymous ,

         

        If the data get refreshed next month, Then the calculated column will be updated at the same time. Assume we update the data in April 2019, then All the value of the calculated column in the rows of March of the table will be "the recent YEAR MONTH". In the slicer, the value is "the recent YEAR MONTH", so in April, the March data will be filtered accrodlingly.

         

        Here I created another column for instance. Assuming we are in April 1 2019.

        Column2 = 
var _today = DATE(2019,04,01)
VAR _yearmonthtoday =
    YEAR ( _today ) * 100
        + MONTH (_today )
VAR _yearmonthPrevious =
    YEAR ( EOMONTH ( _today, -1 ) ) * 100
        + MONTH ( EOMONTH ( _today, -1 ) )
VAR _yearmonth =
    YEAR ( 'CALENDAR'[Date] ) * 100
        + MONTH ( 'CALENDAR'[Date] )
RETURN
    IF (
        _yearmonth = _yearmonthtoday,
        BLANK (),
        IF (
            _yearmonthPrevious = _yearmonth,
            "the recent YEAR MONTH",
            YEAR ( 'CALENDAR'[Date] ) & "-"
                & FORMAT ( 'CALENDAR'[Date], "MMM" )
        )
    )

         

        Regards,

        Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-frfei-msft  Users should be able to see the other month year combinations as well, just that when it refreshes every month, the slicer has the current month - year selection automatically so that I need  not change it each time to set the report page as of that month year. 

       

      But if users would like to switch to another month year the should be able to view those options. I thought adding a code to the "default value" Section in the power slicer helps. 

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago
        Anonymous wrote:

        v-frfei-msft  Users should be able to see the other month year combinations as well, just that when it refreshes every month, the slicer has the current month - year selection automatically so that I need  not change it each time to set the report page as of that month year. 

         

        But if users would like to switch to another month year the should be able to view those options. I thought adding a code to the "default value" Section in the power slicer helps. 

        Hope the Relative slicer could help you.

         

         

        Regards,

        Frank