Dynamic Filter

[Anonymous]

Hi All,

1.  I have a date column in my data table. Based on that single date column only I have to create two new variables/measures(whatever suits better) for Start Date and End Date.

Start Date : Last 10 days + 3 days from today 

e.g. - If today is 10th Sept then Start Date should be 31st August 

End Date  : Today - 3 days 

e.g. - If today is 10th Sept then End Date should be 7th Sept

Data table should have data between start date and end date period only. So in data table, data between 31st August to 7th Sept only should be available. This should be dynamic and as per TODAY  it should change

2. Also,I have to create two dynamic slicers for these dates and it should have all date values 

--Start Date should have values from 31st August to 10th Sept

--End Date should have values from 7th Sept to 10th Sept

Can somebody please help?

Thanks in advance! 

[Anonymous]

Hi @Anonymous Thank you so much for your valudable inputs! I am new to Power BI and your inputs worked!

Purpose of the two dynamic Slicers : I have to create a data refresh information dashboard and based on single date column only end and start date is supposed to be displayed. Hence, two slicers are needed.

Also, I have one more concern. 

In transform data I have loaded data with the help of static variables. I have created 2 variables 

Start Date and put value as 1st Sept

End Date and put value as 12th Sept

I have used these parameters to filter data and based on this dashboard is built. Instead of these static parameters I have to filter data based on dynamic variables/parameters. How that can be done? Can we use already created measure?

[Anonymous]

Hi, @Anonymous 

I am glad to help you.

Before I help you with your problem I would like to ask what is the purpose of the two dynamic Slicers you need to create?

In your description, the Dynamic Slicer contains dates up to today, but the DateTable you are requesting to display contains data only up to Today - 3 days. So what actually is the purpose of the Dynamic Slicer to filter the data?

Based on your description, I have made a simple example, hope it will help you.
First you need to create two measures, StartDate and EndDate:

StartDate = 
VAR Today = TODAY()
RETURN Today - 10

EndDate = 
VAR Today = TODAY()
RETURN Today - 3

Then you need to create another Measure for displaying the data from StartDate to EndDate:

DateTableShow = 
VAR startDate = [StartDate]
VAR endDate = [EndDate]
RETURN
IF(MAX(DateTable[Date]) >= startDate && MAX(DateTable[Date]) <= endDate,1,0)

Then drag the Measure to the Filter panel and set the Measure value to 1:

For the two dynamic Slicers, you can do a New Table for each:

StartDateTable = 
CALENDAR(
    TODAY() - 10,
    TODAY()
)

EndDateTable = 
CALENDAR(
    TODAY() - 3,
    TODAY()
)

Then drag the relevant fields into the fields of the Slicer visual:

The pbix file about this simple example I have posted below, I hope it helps you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak can u please help me ?