Dynamic Date Filtering based on the slicer selection in Power BI

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.

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! 🙂