In Force Policy Slicer

[iblock]

I am using the map visual and want to display policies that are in force as of a specified YYYYMM.  I want to create a slicer such that when the date is selected, it will plot only the policies that satisfy: Eff YYYYMM <= Selected Date <= Exp YYYYMM.

Could anyone help with this?

[v-xinruzhu-msft · Accepted solution]

Hi @iblock 

You can refer to the following example

Sample data 

Date table

Create a measure

IsInDate = var _format=CONVERT(FORMAT(MAX('Date'[Date]),"YYYYMM"),INTEGER)
return IF(ISFILTERED('Date'[Date]),IF(MAX([Eff YYYYMM])<=_format&&MAX([Exp YYYYMM])>=_format,1,0),1)

Then put the measure to the visual filter of Policy Slicer

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[iblock]

I created a parameter called "Inforce Date" and a new column in the data that is calcualted as: 

Inforce? = IF(AND(Premium_Raw[Eff YYYYMM]<=[Inforce Date],Premium_Raw[Exp YYYYMM]>=[Inforce Date]),"Inforce","Not Inforce")

However, the result is coming back "Not Inforce" for every row, which is not correct.

[iblock]

Another Update: I believe the reason why it's not working properly may be because I need to establish a relationship between the two tables, however, I have not been able to sort out what the relationship should be.  Thoughts?