Search code examples
powerbifilteringdaxmeasure

Need to limit the date in a slicer by today's date in Power BI

I am building a report in Power BI Desktop, created a slicer - YearMonthSort - which has data selection by Year-Month

Plz, see the screenshot below:

enter image description here

My goal is to limit data in this slicer as - 2015-07 to today's date (whichever it will be when users will look at the data, in the same format - "YYYY-MM")

In the "Filters" section I can select my starting year as 2015-07, but having problem setting today's date.

I tried to create the new Measure, but not sure where to place it, or, may be there is another way to perform this:

      IsToday = FORMAT(TODAY(), "mm/yyyy")

I only just started to learn this, Thank you for help in advance!

Solution

  • You can do the following:

    I am assuming that you are using a calendar table that is joined to your fact table on a date field and that your year sort column is calculated with reference to the 'date' field in your calendar table.

    Assuming, this is true you can create a calculated column that will flag whether or not the date field is before or after today. This can be achieved by using the following DAX:

    IsToday =
SWITCH (
    TRUE (),
    'Calendar'[Date]
        <= NOW (), 1,
    0
)

    Then, in your visual add a 'Visual Level Filter' using this new column and set it to 1.