Power Slicer Custom Visual

[Anonymous]

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!

[v-frfei-msft]

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]

@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]

---

@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

[Anonymous]

@v-frfei-msft The relative slicer would change to March on April 1st. However, my data loads on April 4th only. 

[Anonymous]

@v-frfei-msft The relative slicer would change to March on April 1st. However, my data loads on April 4th only. 

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Anonymous]

@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]

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]

@v-frfei-msft  Is there a way that it can show "Feb 2019" this month or "Mar 2019" this month rather than "Recent Month Year" ? 

[v-frfei-msft]

Hi @Anonymous ,

Based on my test, it is not possible to meet your requirement currently.You can come up a new idea about that and add your comments there to improve Power BI and make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

Regards,

Frank