Count items where date is max

[Ildar7070]

Hello everybody, 

I have an interesting problem and need your help.

Use Case:

Imagin, you get a list of ticket changes. A Ticket can be in the status "to do", "doing" and "done". You receive an export, that tracks the changes. For example, the statsu of ticket number one, changed from "to do" to "doing". You get a list in the structure as following:

Datum	key	old_status	new_status
01.01.2022	key1		1_to do
01.01.2022	key2		1_to do
01.01.2022	key3		1_to do
03.01.2022	key1	1_to do	2_doing
03.01.2022	key2	1_to do	2_doing
04.01.2022	key1	2_doing	3_done
05.01.2022	key3	1_to do	2_doing
06.01.2022	key2	2_doing	3_done

Goal:

The goal is to create a chart, that shows the number of tickets in a particular status at any given time. Basically a ramp up Curve. 

Approach:

To show the ramp up curve, I should count the number of last changes per ticket untill a specific date.

The query was linked with a date table. 

With the Measure below, I was able to count the number of tickets until a specific date. 

Measure =
CALCULATE(COUNT('Änderungen'[key]),
    'Date'[Date]<=MAX('Date'[Date]),HERE SHOULD BE THE SECOND FILTER EXPRESSION)

Resulting in something like this:

But in here, the Measure is counting all the changes. I only need the last change of every ticket until a specific date. 

Hopefully you can help me out with this. You can find the file via my Onedrive Link here 

Many thanks in advance and I hope you can help me. I really need your help

@amitchandak @Greg_Deckler @Ashish_Mathur @parry2k 

[parry2k]

@Ildar7070 it should be easy. I would recommend creating a sample pbix file and share using one drive/google drive. It will help to provide the solution. If I understood correctly, a ticket can have multiple updates during a day and if you are looking at it by date, you want to put that ticket in the respective bucket for that day, correct?

Again, I would recommend sharing sample pbix. Cheers!!

[Ashish_Mathur]

Hi,

In a simple table, show the exact result that you are expecting.

[parry2k]

@Ildar7070 check this video on my YT channel which addresses something similar. How to get value of each product based on the most recent transaction - Power BI - YouTube

And don't forget to subscribe to the channel 

😌 

Cheers,

P

[Ildar7070]

@parry2k Unfortunately, this is not as easy as I thought. Calculating the last item only is not helping because this only iterates once over the whole table. But I need to get the latest value for each iteration. 

In other words, for each day, give me a table with all the changes that happened until this current day and count only the latest changes per key. Then go into the next iteration and do the same thing for next day