Using a filter as a value in an expression.

[ReubenLam]

Here’s a problem I hope there’s an answer for.

My data table has a list of stores and in that table it includes two date fields (Store Close date and a date when the store becomes a comparative store)

I have a separate date table that I want to use as a filter. They two tables are not currently related/linked

The business requirement is that the users want to be able to set a date in the filter and see all comp stores as of that date filter. They basically want to pick a date and see what stores were comp stores AT THAT TIME.   I thought I could do this by comapring the filter date value to the column values in the table.  Saddly, No.

The rules are:

compdate >= Filter date <= ClosedDate                (Store was a comp store but wasn’t closed at the filter date time frame)

OR

CompDate >= filter date AND ClosedDate is NULL        (store is a comp store and is still open))

How can I use the filter as a value in an expression?

Basically some sort of Calculate(CountA([xxx]), date[date]>= table[CompDate], date[date]<= table[ClosedDate])

Thanks

[dkay84_PowerBI]

Take a look at the following. It sounds like it could be a solution for you (don't let the simplicity of the URL deter you):

http://www.daxpatterns.com/distinct-count/

[ReubenLam]

In this case, that solution doesn't work as there's no relation between the Calendar[Date] table and any of the dates in the store data table.  User picks an arbitrary date from the filter and I want to compare it to two separate dates fields in the store data.  basically using the filter as a value in a logic test.

[dkay84_PowerBI]

Not sure if you have solved this yet but I came across this post and it may help:

http://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONSH...

Also, I am not sure what you mean by "no relationship between the Calendar[Date] table" and the dates in your store data table.  Did you create a relationship?