CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
MalyMajo
MalyMajo

Reputation: 75

Count of last status per day / week

is it possible to get the count of last statuses in a day / week via DAX measure? I want to count only the last status of backlog tickets in a day / week. The backlog is calculated on ticket level (below table Data per ticket) as accumulated difference of count of opened and closed tickets:

Backlog:=
VAR MaxDate = MAX('q_Calendar'[Date])
RETURN
CALCULATE([Opened]-[Closed],CALCULATETABLE(ALL('q_Calendar')),'q_Calendar'[Date]<=MaxDate)

There are 2 source tables, example below:

Data per step

Ticket Number Step Step Date StepStatus
00001 1 8/25/2021 11:06 AM 10
00001 2 8/25/2021 12:05 PM 20
00001 3 8/27/2021 01:13 PM 30
00001 4 8/27/2021 03:33 PM 40
00002 1 8/27/2021 10:01 AM 10
00002 2 8/27/2021 10:05 AM 20
00002 3 8/30/2021 08:47 AM 30
00002 4 8/30/2021 08:59 AM 35
00002 5 8/30/2021 04:25 PM 30
00002 6 8/31/2021 07:49 AM 40

Data per ticket

Ticket Number Date Created Date Resolved TicketStatus
00001 8/25/2021 11:06 AM 8/30/2021 07:51 AM Closed
00002 8/27/2021 10:01 AM 8/31/2021 07:55 AM Closed

So in case the ticket is closed, it should be omitted in the status calculation of that and all future days.

The result should be a bar chart with following data:

Date StepStatus Count
8/25/2021 20 1
8/26/2021 20 1
8/27/2021 20 1
8/27/2021 40 1
8/28/2021 20 1
8/28/2021 40 1
8/29/2021 20 1
8/29/2021 40 1
8/30/2021 30 1

There is a relationship between per step and per ticket data. 1 ticket to many steps. So if I take the StepStatus and use the backlog calculating measure, the results are multiplied per each StepStatus type.

Upvotes: 1

Views: 99

Answers (1)

msta42a
msta42a

Reputation: 3741

For example you can create this kind of table ( physical or virtual (to use inside another mesure)):

Table = 
var __TickAllDayBetween = GENERATE(Sheet1, CALENDAR(Sheet1[Date Created],Sheet1[Date Resolved]))
var LookupTable =  ADDCOLUMNS(SUMMARIZE(ADDCOLUMNS(SELECTCOLUMNS(Sheet2, "TicketNumber", Sheet2[Ticket Number], "StepDate", DATE(YEAR(Sheet2[Step Date]), MONTH(Sheet2[Step Date]),DAY(Sheet2[Step Date]))), "MAX", CALCULATE(max(Sheet2[Step Date]), filter(ALL(Sheet2[Step Date]), DATEVALUE(Sheet2[Step Date]) = [StepDate]))),[TicketNumber],[MAX]),"LastStatusPerDay", CALCULATE( min(Sheet2[StepStatus]), Filter(ALL(Sheet2[Step Date]),[MAX] = Sheet2[Step Date] )))
var __return =  SELECTCOLUMNS(GENERATE(__TickAllDayBetween, TOPN(1,FILTER(LookupTable, DATEVALUE([MAX]) <= [Date] && [TicketNumber] = [Ticket Number]), [MAX], DESC)), "Date",[Date], "Status",[LastStatusPerDay])
return __return

enter image description here

Upvotes: 1

Related Questions