Monthly ticket status across time

[Anonymous]

Hi forum,

I would need to present a ticket status summary at the end of each month based on ticket change log data. I have been trying to find ideas how to get this via various posts but no luck so far. For example this is somewhat close but not exactly fitting for my needs due to different type of source data.

My dataset is like this

Table: Status_Changes

ID	Date	Old_Status	New_Status
100	29.11.2020	status3	status4
102	25.11.2020	status3	status4
104	19.10.2020	status3	status4
103	21.8.2020	status2	status3
104	30.7.2020	status2	status3
104	5.6.2020	status1	status2
103	1.6.2020	status1	status2
100	23.5.2020	status2	status3
102	15.4.2020	status2	status3
101	15.4.2020	status3	status2
101	15.4.2020	status2	status3
101	14.4.2020	status1	status2
100	8.3.2020	status1	status2
100	5.1.2020	status0	status1

There is a ticket ID, the date (or actually time) when the status was changed and the statuses from it was changed and to which status it was changed.

Out of this, I should provide something like this, where I have distinct count of tickets for each month per status

Each ticket should appear into the visual only on the month when the first status change is seen in the data. Once a ticket has reached the final status (status4 in the sample) it should disappear from the visual on the very next month (case ticket ID 104 on November/last column).

I was thinking to transform the source data into something like below where there would be row for each ticket that has been seen in the source data so far for each month and the latest New_Status that has been in the data.

And once a ticket is reached the status4, after a month it should be moved to status5. That can be considered as archived or similar in case that is interesting information later on. Status5 can be then left out from the visual.

But I don't know how to get this with DAX? Or is there some other way to get the visual I need?

Year_Month	ID	Latest_Status
2020-11	100	status4
2020-11	102	status4
2020-11	101	status2
2020-11	104	status5
2020-11	103	status3
2020-10	100	status3
2020-10	102	status3
2020-10	101	status2
2020-10	104	status4
2020-10	103	status3
2020-09	100	status3
2020-09	102	status3
2020-09	101	status2
2020-09	104	status3
2020-09	103	status3
2020-08	100	status3
2020-08	102	status3
2020-08	101	status2
2020-08	104	status3
2020-08	103	status3
2020-07	100	status3
2020-07	102	status3
2020-07	101	status2
2020-07	104	status3
2020-07	103	status2
2020-06	100	status3
2020-06	102	status3
2020-06	101	status2
2020-06	104	status2
2020-06	103	status2
2020-05	100	status3
2020-05	102	status3
2020-05	101	status2
2020-04	100	status2
2020-04	102	status3
2020-04	101	status2
2020-03	100	status2
2020-02	100	status1
2020-01	100	status1

Any ideas are welcome.

[AlB]

@Anonymous 

Place the following M code in a blank query to see the steps. You might have to tweak it a bit to adapt it completely to your needs

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFBDoQgDAXQu7A2TX8L6pzFsPAM6v2lLhRGDOygeb9NYVkcmN3g5EcACYtdtn3dj03vk3dxMCgGQwf0qYLUkVvQKgKaSydZ4mmoTFOPCzSWDFngGYs2ux5GKbSmWgWBfMuh6vQ1t+4++vl/V99jJu1h6XdLlgdiPAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Old_Status = _t, New_Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Old_Status", type text}, {"New_Status", type text}}),
    transform_ = Table.TransformColumns(#"Changed Type",{"Date", each Date.StartOfMonth(_)}), 

    #"Added Custom" = Table.AddColumn(transform_, "NextDate", each List.Select(List.Dates([Date], let aux_ = List.Min(Table.SelectRows(transform_, (inner)=>inner[ID]=[ID] and inner[Date]>[Date])[Date]) in  if aux_ <> null then Duration.Days(aux_-[Date]) else 1 , Duration.From(1)), each Date.Day(_)=1)),
    #"Expanded Next date" = Table.ExpandListColumn(#"Added Custom", "NextDate"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Next date", "Latest_Status", each if [NextDate]= [Date] then [New_Status] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Latest_Status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Date", "Old_Status", "New_Status"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

I mean,

The measure you provided is using the later example table to count the value. But I don't have that table yet. It is just hand made example that I would like to get based on the source data.

I have the source data in PowerBI as table like this

Table: Status_Changes

ID	Date	Old_Status	New_Status
100	29.11.2020	status3	status4
102	25.11.2020	status3	status4
104	19.10.2020	status3	status4
103	21.8.2020	status2	status3
104	30.7.2020	status2	status3
104	5.6.2020	status1	status2
103	1.6.2020	status1	status2
100	23.5.2020	status2	status3
102	15.4.2020	status2	status3
101	15.4.2020	status3	status2
101	15.4.2020	status2	status3
101	14.4.2020	status1	status2
100	8.3.2020	status1	status2
100	5.1.2020	status0	status1

How can I transform that with DAX into table like the one below to be able to visualize the data as expected? This could be then used to calculate the measure you suggested.

Let call this for example Table: Latest_Statuses_Per_Month

Year_Month	ID	Latest_Status
2020-11	100	status4
2020-11	102	status4
2020-11	101	status2
2020-11	104	status5
2020-11	103	status3
2020-10	100	status3
2020-10	102	status3
2020-10	101	status2
2020-10	104	status4
2020-10	103	status3
2020-09	100	status3
2020-09	102	status3
2020-09	101	status2
2020-09	104	status3
2020-09	103	status3
2020-08	100	status3
2020-08	102	status3
2020-08	101	status2
2020-08	104	status3
2020-08	103	status3
2020-07	100	status3
2020-07	102	status3
2020-07	101	status2
2020-07	104	status3
2020-07	103	status2
2020-06	100	status3
2020-06	102	status3
2020-06	101	status2
2020-06	104	status2
2020-06	103	status2
2020-05	100	status3
2020-05	102	status3
2020-05	101	status2
2020-04	100	status2
2020-04	102	status3
2020-04	101	status2
2020-03	100	status2
2020-02	100	status1
2020-01	100	status1

Or, can I get the targetted visualization done by some other way based on the Status_Changes table?

[AlB]

@Anonymous 

I don't understand the question

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thanks, the chart is like expected. I need to test the measure as well but it looks like something I'm looking at.

But how can I transform from the source data table to the target table?

[AlB · Accepted solution]

Hi @Anonymous 

You do not show the numbers/dates in the chart so I'm not sure waht the expected result is exactly but see the attached file for a possible solution. It uses Latest_status in the legend of the chart and this measure: 

Measure = 
VAR status_ = SELECTEDVALUE(Table1[Latest_Status])
RETURN
IF(status_ <> "status5", DISTINCTCOUNT(Table1[ID]))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers