Qualifying Date for Date-Range

[Anonymous]

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?

[Ashish_Mathur]

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.

[v-juanli-msft]

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.

[Anonymous]

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.

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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