Dynamically set slicers Value

[Anonymous]

Hello Team, 

Hi Friends

I have a simple requrement of dynamically loading current year value.

So I have a Year table from which my slicer take current Yr value.

Let say I saved and upload my report with the Year 2020 , now I am in Jan-2021 and I want my report to be open in 2021 Year value default. 

Please provide some solution for this.

Thanks for your help.

@v-yuta-msft 

@FruitandVeg

[Anonymous]

Hi @Anonymous ,

You can refer the following video to achieve it:

Set Default Slicer Selection to Current Year or Month in Power BI

In addition, you can refer the following thread to get the data for current year without using slicer.

Current Year Sales

Best Regards

[Anonymous]

Thanks @Anonymous for your response. 

In my case requirement is simple.  I have simple Date table where "flag" is updated based on some user input which is once in Year. Once my flag is set to 1 i want my report to be open dynamically in 2021 ( which is saved and deployed as 2020 last year) , simple ...I dont have any month or other column

Year     Flag

2019     1

2020     1

2021     0

Desired result 

Year     Flag

2019     1

2020     1

2021     1 - along with when open the report it should open in 2021

[Anonymous]

Hi @Anonymous ,

Whether your report exist the data from year 2021? And how does 2021 input by the user? By slicer, filter or other method? Could you please provide some sample data and your expected result with specific examples? Thank you.

Dynamically Filter By Today's Date

Best Regards

[Anonymous]

"Whether your report exist the data from year 2021 -YES?

And how does 2021 input by the user- "User Run  SP and sp updated this flag in the YearTable" 

Hi I uploaded snaphot of "Current Status" Vs Expected Status - Again i tell you the scenario -

I saved my report with Current Year 2020 since the flag value is 1 for this year - now in Jan-2021 sp run and update this "Flag" value to 1 -- I want my report to be open dynamically in 2021 based in Flag value is now 1 - however at the momvemt if i open the report it displays the value 2021 but bydefault opens in 2020 and then manully i have to change the Year value in slicer , and again upload the report to server , so that next time it open in 2021 .... I want this to dyncmiaclly open in 2021. 

If you provide me your id i can send you sample file as well. 

[Anonymous · Accepted solution]

Hi @Anonymous ，

I created a sample pbix file for you, please check whether that is what you want.

1. Connect to the excel file

2. Create a custom column in Power Query Editor

let
    Source = Excel.Workbook(File.Contents("D:\data\sales.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
     #"Added Custom" = Table.AddColumn(Table1_Table, "Custom", each if [Year]=List.Max(Table1_Table[Year]) then "Default" else [Year])
in
    #"Added Custom"

3. Create a slicer with new custom column and select the option "Default"

Best Regards