DAX formula to count ticket status

[APéczely]

Dear Community,
I have the following output from a ticketing service shown in the table below:

ID	Current status	Created	Current Status Date	Open	In-progress	Contested	For Review	Closed
10001	For review	2024.01.01	2024.02.01	2024.01.01	2024.01.05		2024.02.01
10002	Open	2024.01.18	2024.01.18	2024.01.18
10003	In-progress	2024.01.25	2024.01.31	2024.01.25	2024.01.31	2024.01.28
10004	Contested	2024.01.28	2024.02.03	2024.01.28		2024.02.03
10005	Closed	2024.01.16	2024.02.05	2024.01.16			2024.01.31	2024.02.05

The default status of a ticket at creation is "Open". The last status of a ticket is "Closed". In between, the ticket can have any status, but for simplicity we assume that each status is used only once. 

Date format is "yyyy.mm.dd".

What is the most effective DAX solution to create a time history of the ticket status counts in Power BI as shown in the following bar chart?

Any help would be appreciated.

[Rupak_bi · Accepted solution]

Hi @APéczely 

hope below visual displaying right.

The approach is as below

1. unpivot all 5 different stages in power query. 

2. create a calender table using min and max date of all the stages

date =

var Min_date = CALCULATE(min('Table'[Value]),all('Table'))

var Max_date = CALCULATE(max('Table'[Value]),all('Table'))

return

CALENDAR(Min_date,Max_date)

3. create a calculated table as below

cal_tab =

SUMMARIZECOLUMNS('date'[Date],'Table'[ID],"max date",CALCULATE(max('Table'[Value]),'Table'[Value]<=max('date'[Date])))

5. Add a new column in the above table

new Stat = LOOKUPVALUE('Table'[Attribute],'Table'[Value],cal_tab[max date],'Table'[ID],cal_tab[ID])

6. now create the stack bar chart using the date (X axis), new state (ledgent) and count of new state (Y).
Plz let me know if this works.

[Rupak_bi]

Hi @APéczely ,

I will check your file in some time, meanwhile, I can see, your values have time stamp as well and due to this you are not getting right output. So you need to create a calculated column in the fact table as "new values" and only dates to be extracted from values. Then this new column to be used to create calc_tab. Hope this will solve the issue.

[APéczely]

@Rupak_bi You were right! 'table'[Value] should be of type date instead of datetime! Thank you for your solution!

[Rupak_bi]

Yes . the date column from table cal_tab is used as X -axis. and new stat used as ledgend.  just check it out

Please check your date table formula. the date should start from first january

[APéczely]

@Rupak_bi : My date table is fine. If I comment out the 'cal_tab'[max date] column, the 'cal_tab'[date] column is fine. But once I switch it back, 01 January disappears from the 'cal_tab'[date] column and the dates of status changes are incorrect: 

I run Power BI Desktop version: 2.137.751.0 64-bit (October 2024).

I share my file in case you have time to have a look on it and spot something I did wrong: Status test.pbix 

[Rupak_bi]

Hi, 

Just now I checked my approach and found it is working correctly and there is no date shift. please refer below.

[APéczely]

@Rupak_bi I can't achieve the same! Which date column do you use for X axis: 'cal_tab'[Date]?
This is my table:

The new Stat column formula:

This is my cal_tab:

ID #10001 starts with Open status on 02 Jan instead of 01 Jan and switched to In-progress on 06 Jan instead of 05 Jan.

[Rupak_bi · Accepted solution]

Hi @APéczely 

hope below visual displaying right.

The approach is as below

1. unpivot all 5 different stages in power query. 

2. create a calender table using min and max date of all the stages

date =

var Min_date = CALCULATE(min('Table'[Value]),all('Table'))

var Max_date = CALCULATE(max('Table'[Value]),all('Table'))

return

CALENDAR(Min_date,Max_date)

3. create a calculated table as below

cal_tab =

SUMMARIZECOLUMNS('date'[Date],'Table'[ID],"max date",CALCULATE(max('Table'[Value]),'Table'[Value]<=max('date'[Date])))

5. Add a new column in the above table

new Stat = LOOKUPVALUE('Table'[Attribute],'Table'[Value],cal_tab[max date],'Table'[ID],cal_tab[ID])

6. now create the stack bar chart using the date (X axis), new state (ledgent) and count of new state (Y).
Plz let me know if this works.

[Rupak_bi]

Hi, if my solution works, please accept the reply as solution. 

Truly appreciate your kudos.

[APéczely]

@Rupak_bi : Almost! The concept of your proposed DAX code works pretty well! But the days are shifted by +1 day at the status change days. For example ticket ID #1001 is open since 2024.01.01, but the start is 2024.01.02 on the chart. Also, the In-progress status is set on 2024.01.05, but the chart shows from 2024.01.06.
I'm trying to find a way to shift everything one day back! 

[Rupak_bi]

Hi,

Modify below formula and remove the "=" sign and check.

date",CALCULATE(max('Table'[Value]),'Table'[Value]<=max('date'[Date])))

[APéczely]

@Rupak_bi: Unfortunately, removing the "=" sign doesn't change the result at all, there is still +1 day shift.

[Greg_Deckler]

@APéczely In Power Query Editor, select your first 2 columns, right click and Unpivot Other Columns. The rest is trivial at that point.

[APéczely]

@Greg_Deckler, As a matter of fact, it is all but obvious to me after unpivoting the columns you mentioned. How do I count a given status for a given date when I only have the date of status changes? 

[Greg_Deckler · Accepted solution]

@APéczely I was referring to Open Tickets, should have included the reference. Open Tickets - Microsoft Fabric Community This may need to be tweaked based on your particular requirements but the advantage here is that this approach is dynamic.

PBIX is attached below signature. This involved a calculated column ( which you could make part of the measure if you wanted )

Next = 
    VAR __ID = [ID]
    VAR __Status = [Attribute]
    VAR __Date = [Value]
    VAR __Table = FILTER( 'Table', [ID] = __ID && NOT( [Attribute] IN { __Status, "Created", "Current Status Date" } ) && [Value] >= __Date )
    VAR __MinDate = MINX( __Table, [Value] )
    VAR __Result = IF( __MinDate = BLANK(), MAX( 'Dates'[Date] ), __MinDate )
RETURN
    __Result

and the Open Tickets measure:

Tickets = 
    VAR __Table =  
        SELECTCOLUMNS(
            FILTER(
                GENERATE(
                    'Table',
                    'Dates'
                ),
                [Date] >= [Value] &&
                [Date] < [Next]
            ),
            "ID",[ID],
            "Date",[Date]
        )
    VAR __Table1 = GROUPBY( __Table,[ID],"Count",COUNTX( CURRENTGROUP(),[Date] )) 
    VAR __Result = COUNTROWS( __Table1 )
RETURN
    __Result

[APéczely]

@Greg_Deckler: Thanks a lot! This problem intrigued me since my first Power BI report, as much as you were struggling with your Open tickets DAX code. I still need time to understand how the ticket measure works, but this is definitely a solution!