Forum Discussion
Qualifying Date for Date-Range
Hello all
I have a table of objects.
These objects are available within a data range.
- dateActiveOn = active since
- dateDeadOn = inactive from
I now need to set a qualifying date to show only the objects that are active on that qualifying date.
Until now it just uses "Today" in the formula. (dateActiveOn <= Today) & (dateDeadOn > Today)
Is there a way to let the user set something like a variable that i can use in the formula to filter the objects?
Hi Anonymous
Explaination for the formula as below
qualifying1 = IF ( [selected date] < MAX ( Sheet5[datedeadon] ) //"max" -> get the current row of a column, && [selected date] >= MAX ( Sheet5[dateactiveon] ), 1, 0 )I make a new test as below
in my test, [DeadActiveOn] is date/time type, DateDeadOn and Date (Date table) are in date type.
Could you share a screenshot to let me know your data and what's error of the qualifying measure?
Best Regards
Maggie
5 Replies
Hi Anonymous
Create a date table which is not connected to your data table.
Date table = ADDCOLUMNS ( CALENDARAUTO (), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ), "day", DAY ( [Date] ), "weeknum", WEEKNUM ( [Date], 2 ), "weekday", WEEKDAY ( [Date], 2 ) )scenario 1: select one date from the "date table"
create measures in your main data table
selected date = SELECTEDVALUE('Date table'[Date]) qualifying1 = IF ( [selected date] < MAX ( Sheet5[datedeadon] ) && [selected date] >= MAX ( Sheet5[dateactiveon] ), 1, 0 )add "year","month","day" from the "date table" in the slicers,
add measure "qualifying" in Visual level filter.
scenario 2: select a date period from the "date table"
create measures in your main data table
min = MIN('Date table'[Date]) max = MAX('Date table'[Date])qualifying2 = IF ( [min] >= MAX ( Sheet5[dateactiveon] ) && [max] < MAX ( Sheet5[datedeadon] ), 1, 0 )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello v-juanli-msft
Thanks for this answer.
Im looking to get scenario 1 to work.
Its about the same direction i tried yesterday night, but with a few changes...
For now, i dont get it to work.
Following Questions:
- If DateDeadOn, DeadActiveOn and Date (Date table) may have different formats i.e Date with time or without. Does this matter?
- In the formula, its possible to say DateDeadOn.Date -- Should i use Date?
- The 2 above doesnt really matter i think, the qualifying formula wont work this way. Why use Max?
If the object hasnt a specified deadOn date its 12.31.9999, max is getting this date isnt it?
The qualifying measure is always false, cant get it to true
Hi Anonymous
Explaination for the formula as below
qualifying1 = IF ( [selected date] < MAX ( Sheet5[datedeadon] ) //"max" -> get the current row of a column, && [selected date] >= MAX ( Sheet5[dateactiveon] ), 1, 0 )I make a new test as below
in my test, [DeadActiveOn] is date/time type, DateDeadOn and Date (Date table) are in date type.
Could you share a screenshot to let me know your data and what's error of the qualifying measure?
Best Regards
Maggie
Hello v-juanli-msft
With my inital matrix-table it showes 15k objects less than it should be. Because of that i thought it just dont work.
I made a new table with the ID, activeOn, deadOn and qualifying. When i count the qualifyings (true) i get the correct number.
So your solution works, i just need to implement it into the matrix correctly.
Hi,
It is possible to do this with the Query Editor. The 2 date columns can be merged into single one. To get more specific help, share some data and show the expected result.
