Cumulative count Statuses week by week

[oseasgomes]

Hi Team

  I am struggling to create a visualization for my table.  I have a table (sample below) with historical statuses changes per ticket:

from my sample data:

week_start_date = Monday

up_to = Sunday.  So for the week_start_date I should show counts till up_to

I need to create a summary table like that:

I can have multiple statues for the same ticket per week, but I just want to show the latest status till the up_to column.

I should show cumulative counts for all statuses, except "Done" that I need to show only the counts for last week

Any magical DAX for that?

Samples data are available here: 

https://drive.google.com/drive/folders/1qE9kTJL_UW-ubNT2p76eDDK_LqugDNjf?usp=sharing

Thanks in advance

[oseasgomes]

Hi @amitchandak , Thanks for bringing attention to my problem. Based on your suggestion, I did some tests and I was able to find a solution (from this video: https://www.youtube.com/watch?v=hidJ5T_DYQ0&t=9s) that helped me to summarize my table.

Now, I have just one line per ticket with latest status by week (exactly what I was expecting):

What I need to do now is my summary table. Should be like this one below based on the above snipt:

Noted, ticket TIN-13361 was in Waiting status for the week of 06/06/22, but the next week (6/13/22) this ticket moved to a new status (Done) and my summary is no longer showing this ticket as Waiting but Done.

Basically speaking, for every week I want to see a full picture of the various tickets up to last week. I mean, my data has tickets since 01/01/19 so the weeks should show de data like that:

Week	show data for
6/20/2022	>=01/01/19 and <=06/20/22
6/13/2022	>=01/01/19 and <=06/12/22
6/6/2022	>=01/01/19 and <=06/05/22
5/30/2022	>=01/01/19 and <=05/29/22

[amitchandak]

@oseasgomes , Not very clear. But create this column and try to use that as the filter

if( [Event_timestamp] =

maxx(filter(Table, [Ticket] =earlier([Ticket]) && [week Start Date] = earlier([week start Date]) ), [Event_timestamp]) , 1, blank())