Getting data based on the last date in a matrix

[rep1ay]

Hi,

I'm new in powerbi and I struggling to create a matrix based on updates in a table. What I want to get is the following: 

The columns are the dates.

In the lines I have tickets (from a ticketing system) which beling to an account. 

The values shall be the last remining estimte for those tickets. 

My problem is that I dont hat a "remaining estimate" for each date for each ticket. In my table I just have the updates of that remaining estimate at the time the update was made. 

Here is the example of one of the tickets: 

The column "field" with the value "timeestimate" has the updated data of my remaining estimate for that ticket. 

With a filter on "timeestimate" it looks as follows: 

In my matrix, which I showed above, I want to have the remaing estimate for each date. In the case of that ticket "MEP-54" that would mean: 

02.08.2021: 8100 

03.08.2021: 8100 (not in the table)

04.08.2021: 900 

05.08.2021: 0 

And of course I don't just want it for that one ticket but for all tickets belonging to that account including a sum. 

I hope somebody can help me. 

Many thanks and cheers

Timo

[Anonymous]

@rep1ay 

I assumed you had an separate calendar table. You can just append the fill down the blanks. For example, 2021.1.2 is missing in the below table. You can merge it with the calendar date and fill down value. Check applied steps in the pbix.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[rep1ay]

Hi Paul,

many thanks for your reply. 

I was considering doing that.

Only ... it's not just one ticket, but thousands. I would need a table with all the dates and with all the tickets. Therefore I thought it would be a better solution to create some kind of measure that gets the data on the fly in a report. 

Regards

Timo

[rep1ay]

Nobody an idea? 😞