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

~ nikilprabhakar

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

Published by nikilprabhakar

10 thoughts 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?

    Reply

  2. Is it possible to automatically update a data slicer to reflect a time period (week/month/quarter) and not just a single day?

    Reply

    1. can u able to tell me dax query for month base which is having one year data ,i need dax query based on current month(my current month is march).

      Reply

  6. Please note that the Function ‘MAX’is not allowed as part of calculated column DAX expressions on DirectQuery models. When models are imported, it is working like a charm -Thanks

    Reply

  7. Very good article. Please note: The function MAX is not allowed as part of calculated column DAX expression on DirectQuery models. However; when models are imported – it is working like a charm.

    Reply

Leave a comment