Dynamic Slicer

[Anonymous]

I have a slicer called month year which is set to Aug 2018. so when i publish it to server, the default view of the page would be the visuals showing for Aug 2018. However, in Sept 2018, i would like that month year to change to sept 2018.

Is there any way this could be done dynamically? Or is the only way to do this to keep setting this manually?

If not for a slicer, is there any way to set this page dynamically?

[v-frfei-msft]

Hi @Anonymous,

We can create a calcualted column to work on that.

Column = 
var yearc = YEAR(Table1[date])
var monthc= MONTH(Table1[date])
var yeart = YEAR(TODAY())
var montht = MONTH(TODAY())
return
IF(yearc=yeart && monthc=montht, "the last month",yearc & FORMAT(Table1[date],"mmm"))

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

@v-frfei-msft Hey this would just filter my slicer for one month then and my users will not be able to see the other dates.

Now i have the month year slicer set to Aug 2018 and all my visuals on the page are filtered for that. But once i move to october, this month year needs to be September 2018 (On october 4th this needs to change when data refresh happens). But since the page is set to aug 2018 that becomes the default.

I want to make this process automated though. So if there is any way to set the page as of the previous month year (On the 4th of the present month) then it would be great! Maybe not neccesarily by using slicers, but any other method.

[BobBI]

Hi Akshaya,

If you achieve this in couple of different ways.

Approach 1 :

Create a 'Current Month' column in your Date table ( if you don't have one , i would suggest to make a habit of using Date dim, very helpful).

Current Month =  if(
                                 Year('Date'[Date]) = Year(TODAY()) && MONTH('Date'[Date]) = MONTH(TODAY())

                                ,0

                               ,BLANK()
                               )

and  use this field as Visual level filter - Filter Type= Advanced Filtering

Show item when value = is

and enter 0 in the value ----apply filter.

use this as per your requirement at visual or page level.

Approach-2

Use slicer - drag 'Date' field to it. Use 'Relative' option and make desired selection.

Relative OptionSliver with current month

Hope this helps. Please let me know if this does 🙂

Good luck.

Bob.

[Anonymous]

@BobBI Hey i can use this. But as you can see in the following picture, I have set it to Sept 1- 30

 But the problem is when it moves to November, it will take Oct 1 - 31 in November. But this should become Oct 1 - 31 only on November 4th because that is when the data refreshes.  So if it changes to October 1 - 31 on November 1st my page will be blank.

[BobBI]

Hi ,

Did you try approach1 ? if it doesn't work i will create a full working example and send it over to you.

Approach 1 :

Create a 'Current Month' column in your Date table ( if you don't have one , i would suggest to make a habit of using Date dim, very helpful).

Current Month =  if(
                                 Year('Date'[Date]) = Year(TODAY()) && MONTH('Date'[Date]) = MONTH(TODAY())

                                ,0

                               ,BLANK()
                               )

and  use this field as Visual level filter - Filter Type= Advanced Filtering

Show item when value = is

and enter 0 in the value ----apply filter.

use this as per your requirement at visual or page level.

Thanks,

Bob

[Anonymous]

@BobBI It would be great if you can show the working! Thanks!

[PattemManohar]

@Anonymous Try using "Relative Date Filter"

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range