Max/latest value per category based on slicer value

[jhhu]

Hi!

Couldn't find a solution to this anywhere so I decided to ask about it myself.

So,

my data consists of events, and these events have multiple tasks associated with them. These tasks can be added and deleted multiple times from an event. Basically I have a table that includes these additions/deletions and this is illustrated in the picture below.

I want to find out which tasks belonged to an event at a specific time. I'm trying to do it with a slicer, where I put the date I want, and then based on the slicer selection I should see the row with the latest date for each task. (instead of date could also use some kind of assignment ID based on order of assignment to avoid the issue of dealing with multiple assignments on the same date)

For example, if I set the maximum date in the slicer as 1.1.2016, the highlighted rows from the data should be visible in a table in my report, as these rows are the latest before the slicer max date.

From the highlighted rows I can then pick the ones that have AssignmentType="Added" to find out which tasks belonged to the event at 1.1.2016.

Does anyone know how to do this? I know that I can get the maximum date for each task by creating a calculated column that utilizes the EARLIER function, but that only gives the overall maximum instead of maximum based on the slicer selection. If the max value was a measure, it would change with the slicer as needed, but as far as I know, measures can only have one value and I need to pick as many rows as I have tasks in an event.

Any help would be appreciated!

[v-juanli-msft · Accepted solution]

Hi @jhhu

1. Create a date table and create relationship between two tables.

Table = CALENDARAUTO()

2. add the "date" column to the slicer, then create a measure

selected date = SELECTEDVALUE('Table'[Date])

3. create measures in the data table

latest per category =
CALCULATE (
    MAX ( [assign date] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[Event id], Sheet2[task id] ),
        [assign date] <= [selected date]
    )
)

flag = IF(MAX([assign date])=[latest per category],1,0)

4. set conditional formatting for the table,

Reference :

Conditional formatting in tables

s

Best Regards

Maggie

[jhhu]

@v-juanli-msft Thanks a lot! This seems to do the trick.