Power BI Tutorial: Dynamically update Date slicer to show Current Date

One comment

Here is a common requirement that I have come across: “I want the Date slicer to automatically select current date without me having to manually change the slicer”. Here is a way to do that.

We have a Power BI Desktop report that was created on April 10th 2016. When the report was created, we set the slicer to 4/10/2016.

DateSlicerImg1

Couple of days later, we refreshed data and this is how the report looks now. We have the new date values, but slicer continues to point to the previously selected date.

DateSlicerImg2

To update the slicer dynamically, let’s introduce a calculated column

SlicerDate = IF(‘Table’[Date]=MAX(‘Table’[Date]),”Most Recent Date”,’Table'[Date]&””)

We are setting maximum date to a text field called “Most Recent Date”. If it’s not the most recent date, we are returning the original date. Since “Most Recent Date” is a text field, we need to convert false condition to text as well, hence we concatenate empty space (‘Table'[Date]&””).

Now replace Date slicer with SlicerDate slicer.

DateSlicerImg3

Notice, new SlicerDate column is not sorted right. Let’s use Sort By Column feature to sort the column based on Date

DateSlicerImg4

Now SlicerDate is sorted as expected.

We refresh data after a day and get a few more data rows. Notice SlicerDate field is updated dynamically to select the new maximum date.

DateSlicerImg5

1 comments on “Power BI Tutorial: Dynamically update Date slicer to show Current Date”

  1. I have tried to replicate your solution with our environment using Power View in SharePoint with a tabular model in SSAS with little success. Any thoughts you can share?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s