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)
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])