Latest Status per ID based on Date Slicer

[Anonymous]

Hey Datanauts,

I am hitting a roadblock in my project and could really use your help. I have a table which record the timestamp and change in status of each id as shown below:

Ticket_ID	Edit_Datetime	Change_Status
001	1/5/2020 12:00	New
001	1/6/2020 12:00	Approved
001	1/7/2020 12:00	Investigating
001	1/7/2020 14:00	Closed
002	1/6/2020 12:00	New
002	1/7/2020 13:00	Approved
002	1/7/2020 16:00	Escalated
002	1/9/2020 08:00	Closed

I am trying to extract the latest value per ID based on a max value of  a date slicer. For example, if i set the max date slicer to 1/8/2020, I hope to get the following result.

Ticket_ID	Edit_Datetime	Change_Status
001	1/7/2020 14:00	Closed
002	1/7/2020 16:00	Escalated

I have tried LASTNONBLANK, MAX, LASTDATE, but cant get to the right DAX expression. This is my current expression:

Latest Status=

var Max_Date = MAX('Table'[Edit_Datetime])

RETURN

CALCULATE(LASTNONBLANK('Table'[Change_Status],1), 'Table'[Edit_Datetime] <= Max_Date)

Thanks for the help in advance.

[v-lili6-msft · Accepted solution]

hi @Anonymous 

For your case, just use this formula

Latest Status = 
var Max_Date= CALCULATE(MAX('Table'[Edit_Datetime]),FILTER(ALLSELECTED('Table'),'Table'[Ticket_ID]=MAX('Table'[Ticket_ID]))) return
CALCULATE(MAX('Table'[Change_Status]), FILTER('Table','Table'[Edit_Datetime] = Max_Date))

Result:

and here is sampel pbix file, please try it.

Regards,

Lin

[Anonymous]

This is great thanks. It works great when using a dates slicer without any other external filters. How can I adapt this measure to ignore other external filters. e.g. in my table we have a 'stage' column. when I don't have any external filters it shows 5 records for stage x. But when I use an external filter (slicer) to slice the table by stage x it shows 9 records. The desired result is to still show 5 even when filtered externally. I hope this makes sense.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.

[Anonymous]

Hi Ashish,

Thanks for getting back to me so promptly. I'll share some screenshots of the issue I'm facing, and if needed will share some data.

In both screenshots the table visual is filtered where the latest status measure is not blank. We use the load date slicer to see the latest versions of references and their statuses during the date range selected. This works fine and as you can see we are seeing 11 references with 'approved' status. This is the desired result.

However, in the second screenshot I've set the status slicer to approved, but the table visual shows 19 records. The expected result is that the result should be 11 approved records (i.e. the same records as the first screenshot).

Thanks in advance.

[Ashish_Mathur]

Hi,

What happens when you turn off interaction between the status slicer and the visual?

[Anonymous]

Hi Ashish, when I turn off the interaction the table visual has no reaction to the slicer (i.e. nothing changes). This isn't the desired result as we would still want to isolate the status according to what's selected in the slicer (e.g. show the approved count in the first screenshot as per the example above and not the 19 records in the second screenshot).

Thanks in advance.

[Ashish_Mathur]

My suggestion should work.  Please retry.

[Anonymous]

Hi Ashish, what is the suggestion? If, turning off the intercation between the status slicer and the table visual, this doesn't work (see my repsponse above). Apologies if I'm confusing matters.

[Ashish_Mathur]

Share the download link of the PBI file.

[Anonymous]

@v-lili6-msft Than you for your help once again. I tried your measure and it worked perfectly for showing the latest status, but when i ask it to count the number of tickets with a particular latest status, it will pop an error. I tried to modify the measure you gave, but I am not that well versed with the filter function in your measure. Would appreciate your help if you can. 🙂 

[Anonymous]

Hey @v-lili6-msft, 

That worked perfectly. Thanks so much for your response. 😀

[amitchandak]

Try

Latest Status=
var Max_Date = MAX('Table'[Edit_Datetime])
RETURN
CALCULATE(LASTNONBLANK('Table'[Change_Status],1),values(Ticket_id), 'Table'[Edit_Datetime] <= Max_Date)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601