Dynamic Filtering Table

[RebeccaWilson]

Hello, 

I am trying to get some dynamic filtering going on a Power Bi model im working on. I have a list of contracts and their start and end dates. I want the user to be able to pick a month (on the dashboard) and it will show them on the dashboard the contracts that are live on these dates. 

I did manage to get it working for if the contract start date is the same as said month but im having trouble with the measure formula to include contracts that are live. 

To sepcify i need the contracts to show where StartDate<SaidMonth and EndDate> Said month.

Does anyone have a solution?

Thanks

[v-juanli-msft]

Hi @RebeccaWilson

In Home->Enter Date, enter month value from 1 to 12, then in this table, create a measure

selectmonth = SELECTEDVALUE(Table1[month])

Then In the orginal table,create calculated columns

startmonth = MONTH([start date])

endmonth = MONTH([end date])

Next create a measure

Measure = IF([selectmonth]<=MAX([endmonth])&&[selectmonth]>=MAX([startmonth]),1,0)

Finally, add this measure to the Visual level Filter

Best Regards

Maggie

[RebeccaWilson · Accepted solution]

Thansk for the help guys, My collegue managed to find a solution here:

https://community.powerbi.com/t5/Desktop/Filtering-within-a-date-range/td-p/227767

I will try and test your solutions out when i get chance. Always good to know multiple ways to do things. 

[SivaMani]

@RebeccaWilson,

Try the below logic,

Num of Contracts = CALCULATE(COUNT([Contracts  ID]),FILTER(TableName,StartDate<SaidMonth && EndDate> Said month))