Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NitaT
Frequent Visitor

Automatically update a slicer based on day of week

Hello! I am new to Power Query and need some help with a slicer. 

I have a DateDim set up using this code (thank you @dkay84_PowerBI ) with a few amendments, as my week starts on a Sunday and ends on a Saturday.  I am using the FY week number and FY columns as slicers to display the current financial year and week, but have to manually select the new week on Monday.  

Note : the financial year is the 1st March, but reporting starts on the Saturday before, i.e. for this year the start of the new financial year is the 28th Feb

 

NitaT_1-1620129144408.png

 

I have looked through various threads and tried to add a current week column with no luck 😞 and have added a calculated column as an alternative with the following formula: 

=IF(Today()=[Date],DateDim[FYWeekNum])

Is there a way to add a calculated column that will automatically amend the slicer to select the new week on a Sunday?

Any help would be most appreciated.

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @NitaT ,

 

Currently, we are not able to set default value as you describe and disable the ability for user to cancel choice in slicers. 

Although this isn’t natively supported as of yet, we can imitate this functionality with a little bit of DAX! A general overview of the process to accomplish this task is as follows:

  1. Create a slicer.
  2. Create a measure to determine how many selections are currently made on the slicer. We’ll call this Current Selections.
  3. Create a measure to determine the total number of possible slicer selections. We’ll call this Total Selections.
  4. Create a final measure using logic from the previous two steps. If Current Selections = Total Selections, then something, else something (We’ll fill this in later).

 

Reference: Setting a Default Slicer Selection

 

 

Best Regards,

Stephen Tao

 

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

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @NitaT ,

 

Currently, we are not able to set default value as you describe and disable the ability for user to cancel choice in slicers. 

Although this isn’t natively supported as of yet, we can imitate this functionality with a little bit of DAX! A general overview of the process to accomplish this task is as follows:

  1. Create a slicer.
  2. Create a measure to determine how many selections are currently made on the slicer. We’ll call this Current Selections.
  3. Create a measure to determine the total number of possible slicer selections. We’ll call this Total Selections.
  4. Create a final measure using logic from the previous two steps. If Current Selections = Total Selections, then something, else something (We’ll fill this in later).

 

Reference: Setting a Default Slicer Selection

 

 

Best Regards,

Stephen Tao

 

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

v-stephen-msft
Community Support
Community Support

Hi @NitaT ,

 

Sorry, not very clear. What do you want to make the current week? Can you display a expected result?

 

 

Best Regards,

Stephen Tao

 

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

Hi @v-stephen-msft ,

Sorry my query was not very clear...let me try again:

I am using a financial year and weeknumber slicer, to filter the pivot table by the days of the week. The reporting week is Sunday to Saturday.  In the example below the dates displayed are for fiscal week 10, on Monday week 11 will be added to the "Select Week Number" slicer as the data for Sunday will be available.  Currently, on Monday, I will need to manually select week 11, to display the sales data for the new week.

What I would like is for the slicer to automatically select week 11 when the day changes to Sunday. 

NitaT_0-1620341379758.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors