Did you get my request answer?

hi, Anonymous 

Best my test on your sample pbix file, The reason should be these two points:

1. you may try to use this formula to create a flag measure

flag = IF(DATEDIFF(TODAY(),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY)>=0,DATEDIFF(TODAY(),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY))

2. For your dataset, I found that in your formula, you used [Lease End Date - Copy] column, but in the visual, you used [LED] as Year and [FiscQtr] as Quarter. But [FiscQtr] is not defined by [Lease End Date - Copy].

If you should add a quarter column based on [Lease End Date - Copy], then use it in the visual.

New FiscYr = "Q"&FORMAT(data[Lease End Date - Copy],"Q")

Result:

and here is sample data, please try it.

Best Regards,

Lin

One thing which should be there, when i select month then the table should be replect data that of from the date 14 from that month

hope i am crear on this, you are very near to my results

hi, Anonymous 

"when i select month then the table should be replect data that of from the date 14 from that month"

what is the month do you select, and what is the date 14?

could you explain the output with the sample pbix file?

Best Regards,

Lin

Sorry! my Bad,

I mean i have data for last 12 months and i have merged them, i wish to filter visual according to month selection. 

14 is the date of the each month or you can assume the 1st date of the each month for filter that month's data with upcoming lease expiring details, like you did this month's date "10/14/2019". but i am looking for results according to each month's 1st date.

or 

do i need to add column date.firstdayofthe month instade of column "Today". and it should be in query editor.

What you think.

Hope i am clear on my query.

hi, Anonymous 

It's not sure that I understood it correctly, do you want a date for slicer, then choose on date and use it as "Today" date in the measure?

If so, you could just use 'Fiscal Calendar'[Date] as a slicer and adjust the formula as below:

Measure flag = IF(DATEDIFF(SELECTEDVALUE('Fiscal Calendar'[Date],TODAY()),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY)>=0,DATEDIFF(SELECTEDVALUE('Fiscal Calendar'[Date],TODAY()),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY))

note: 'Fiscal Calendar'[Date] must be a separate date table, if you need Fiscal Calendar create a relationship with data table, you need to change it with a separate date table.

Best Regards,

Lin

i wish to be filter data in power query. so i can work arround it