Dynamic Date Filtering based on the slicer selection in Power BI

[AshuAnand123]

I have a requirement where i need to filter the dates based on the slicer selection which would be controlled by the end user. So the slicer would be month name (Jan, Feb, Mar,.....,Dec) or month number (1,2,3,...,12). I want to filter the dates till that month for each year. 

For example if the user selects May or month number 5, I want to show Jan to May months for each year in the dashboard. I have tried to make calculated columns or measures as well, but unable to make it dynamic. 

I am able to do if i hard code the month name or number in my calculation, but I have to make it dynamic.

Any thoughts would be appreacited on this.

[danextian · Accepted solution]

Hi @AshuAnand123 

The simplest solution is to use the before feature of a date or numeric slicer so the min date/number  up to the max is selected. For text slicers, you will need to use a disconnected table, reference that in a measure and use the column from that table in the slicer.

Sample DAX

Sum of Value2 =
CALCULATE (
    [Sum of Value],
    KEEPFILTERS ( DatesTable[Date] <= MAX ( DisconnectedDatesTable[Date] ) )
)

Please refer to the attached pbix.

[Sergii24 · Accepted solution]

Hi @AshuAnand123, this is not a simple task: you'd need to have a good knowledge of data modelling and DAX.

So if you want click on a value of month and make it filter months themselves, the one you click on should be different from months you want to filter. This is why you'd need a disconnected table. You can build one in the following way:

Months Disconnected = 
    SUMMARIZE( 
        DateTable,
        DateTable[Month Name], DateTable[MonthNum]
    )

Now you should have 2 tables related to dates: "DateTable" (the one connected to all other tables in your data model) and "Months Disconnected". If you create a filter "Month" from "Months Disconnected" and start clicking on values, nothing will happen (because it's disconnected).

Now you need to make it interact with your model. To do so, you need to write a measure similar to what @danextian has suggested to you. The idea is that connected "DateTable" should be filtered by the value from the diconnected one. This apporach will work if you have only 1 measure to work with.

In case you have more measure in your report and you need to apply the same logic again and again, this is where "Calculation Groups" enter to the game. By using them you can write a logic like this one that will apply the same calculation over any measure of your report (below is a calculation item of a new calculation group):

DateInScope = 
VAR _DisconnectedMonth = MAX( 'Months Disconnected'[MonthNum] )
RETURN
    IF(
        COUNTROWS(
            FILTER(
                DateTable,
                DateTable[MonthNum] <= _DisconnectedMonth
            )
        ) <> BLANK(),
        SELECTEDMEASURE (),
        BLANK()
    )

Follow suggestions of @johnt75 and use this calculation item as a page filter, so all measure will now be filtered accordingly.

Now you should have a clear picture of suggestions provided to you 🙂

To make it simple, go with @danextian  suggestion if you basic knowledge to PowerBI and have a single measure. Instead if you need to build it for more complex solution, go with @johnt75 calculation groups.

Good luck with your project! 🙂

[AshuAnand123]

This was helpful using the disconnected date table concept. And definately all the measures have to be updated in the case and we have to use calculation group for that.

[johnt75]

If you want to apply the filter to every measure on the page you could create a calculation group with a single calculation item

YTD =
CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( DATESYTD ( 'Date'[Date] ) ) )

and then apply that calculation group as a page level filter.

[danextian · Accepted solution]

Hi @AshuAnand123 

The simplest solution is to use the before feature of a date or numeric slicer so the min date/number  up to the max is selected. For text slicers, you will need to use a disconnected table, reference that in a measure and use the column from that table in the slicer.

Sample DAX

Sum of Value2 =
CALCULATE (
    [Sum of Value],
    KEEPFILTERS ( DatesTable[Date] <= MAX ( DisconnectedDatesTable[Date] ) )
)

Please refer to the attached pbix.