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
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.
Not sure if you have solved this yet but I came across this post and it may help:
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?
User | Count |
---|---|
75 | |
37 | |
33 | |
16 | |
13 |
User | Count |
---|---|
83 | |
30 | |
26 | |
16 | |
13 |