Filter Measure Between Dates

[Anonymous]

I have data which has a from and to date, with the date it is created in the from and the date its no longer active in the to column. I created a filter measure so I can selected a date from a disconnected date table, and if the risk created date is before the selected date and the end date is after then show the data. Also if the risk is still ongoing the to date is blank (null).  This is the measure I created;

Active Threats = IF([Selected Date]>MAX('Risk Data'[FromTT])&&IF(ISBLANK(MAX('Risk Data'[ToTT])), TRUE(), [Selected Date]<MAX('Risk Data'[ToTT])), 1, 0)

I then filter my table using this measure.

This is my data, I'm expected when I filter the date to before the 16th of jan, for the 1 extreme and 1 moderate risk to drop off.

However this only happens if i keep the date column in there, otherwise all the moderate risks drop off. Any ideas why this is happending? Test data Here: https://drive.google.com/file/d/1iEN5Iz5U9h0pEPf_bRZw9x3LFeGTClYC/view?usp=sharing

[Anonymous · Accepted solution]

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

[v-lili6-msft · Accepted solution]

hi @Anonymous 

---

@Anonymous wrote:

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

---

I have test on my side, This formula works well, and what is " I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID"?

Regards,

Lin