Forum Discussion
Best Practices for Complex Date Filters
- 4 years ago
Hamzehn , You can try like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(contracts_table[Value]), filter(contracts_table, contracts_table[expiry_date] >= _max || ISBLANK([expiry_date])) )
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(contracts_table[Value]), filter(contracts_table,contracts_table[contract_status] <> "CANCEL" || contracts_table[cancellation_date] > _max
) )
Hamzehn , with many dates you have approach with one active join and no active join. File attached for both after signature
blog to refer
Thank you for taking the time to respond to my post. I had contemplated creating a calendar table with actual dates and using it that way, but I ran across a couple of questions/challenges that I couldn't answer:
- Will this approach work if I need to use two (or even three) different date relationships in the same CALCULATE? For example, in the complex measure I mentioned above, I need to filter based on three date columns (application_date, expiry_date, and cancellation_date)
- How do I replicate the OR logic in some of the measures, like:
contracts_table[expiry_date] >= period_start || ISBLANK([expiry_date])
or,
contracts_table[contract_status] <> "CANCEL" || contracts_table[cancellation_date] > period_end
both of which are being done in the same measure too
Would really appreciate your input on these.
Thanks again.
- amitchandak4 years ago
Super User
Hamzehn , You can try like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(contracts_table[Value]), filter(contracts_table, contracts_table[expiry_date] >= _max || ISBLANK([expiry_date])) )
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(contracts_table[Value]), filter(contracts_table,contracts_table[contract_status] <> "CANCEL" || contracts_table[cancellation_date] > _max
) )