CODEWITHSUNDEEP
powerbidaxdaxstudio
David Molina
David Molina

Reputation: 161

Count opened tickets

I have to count opened tickets between a period. My table has this structure

idissue creation statusChangedDate Status
100 2024/06/01 2024/06/29 Closed
200 2024/06/10 2024/06/29 Opened
200 2024/06/01 2024/06/30 prepared
200 2024/06/10 2024/03/07 Closed
300 2024/08/01 2024/08/01 Opened
300 2024/08/01 2024/09/03 prepared

I've tryed to solve from different ways but I can't achieve it

First

VAR _Month = SELECTEDVALUE(DimCalendarIssues[Month])
VAR _Year = SELECTEDVALUE(DimCalendarIssues[Year])
VAR _FinMes = EOMONTH(DATE(_Year, _Month, 1), 0)

RETURN
    CALCULATE(
        DISTINCTCOUNT(Fac_Jira[IdIssue]),
        FILTER(
            Fac_Jira,
            Fac_Jira[StatusCreatedDate] >= DATE(2024,06,01)
            && Fac_Jira[StatusCreatedDate] <= _FinMes 
            && NOT(Fac_Jira[IdStatus] IN {[Closed], [Finished], [Cancelled]})
            && Fac_Jira[StatusCreatedDate] = 
                CALCULATE(
                    MAX(Fac_Jira[StatusCreatedDate]),
                    Fac_Jira,
                    Fac_Jira[IdIssue] = EARLIER(Fac_Jira[IdIssue])
                    && Fac_Jira[StatusCreatedDate] >= DATE(2024,06,01)
                    && Fac_Jira[StatusCreatedDate] <= _FinMes 
                )
            )
    )

second using intermediate table

VAR _Month = SELECTEDVALUE(DimCalendarIssues[Month])
VAR _Year = SELECTEDVALUE(DimCalendarIssues[Year])
VAR _FinMes = EOMONTH(DATE(_Year, _Month, 1), 0)

VAR _lastStates = 
    SUMMARIZE(
        FILTER(
            Fac_Jira,
            Fac_Jira[CreationDate] > DATE(2024, 06, 01)
        ),
        Fac_Jira[IdIssue],
        "LastDateState", MAX(Fac_Jira[StatusCreatedDate]),
        "LastState", 
            CALCULATE(
                MAX(Fac_Jira[IdStatus]),
                FILTER(
                    Fac_Jira,
                    Fac_Jira[IdIssue] = EARLIER(Fac_Jira[IdIssue]) &&
                    Fac_Jira[StatusCreatedDate] = MAX(Fac_Jira[StatusCreatedDate])
                )
            )
    )
VAR _Filtered =
    FILTER(
        _lastStates,
        [LastDateState] <= _FinMes &&
        NOT([LastDateState] IN {[Opened], [Finished], [Cancelled]})
    )

RETURN
    COUNTROWS(_Filtered)

Neither works well.

I appreciate your help.

Upvotes: 0

Views: 64

Answers (2)

Angelo Canepa
Angelo Canepa

Reputation: 1791

A more straightforward approach is to use Power Query to expand your tickets table. Preferably, the table should be expanded in Power Query (Data ingestion) instead of the data modelling layer (DAX) due to model performance. Expanding the table has the benefit of using the Storage Engine, instead of the formula engine, making your calculation faster.

More info here:
https://www.sqlbi.com/articles/formula-engine-and-storage-engine-in-dax/

Assuming FactJira look like this:

idissue creation statusChangedDate Status
100 01/06/2024 29/06/2024 Closed
200 10/06/2024 29/06/2024 Opened
200 01/06/2024 30/06/2024 prepared
200 10/02/2024 07/03/2024 Closed
300 01/08/2024 01/08/2024 Opened
300 01/08/2024 03/09/2024 prepared

I modified row 4, the creation column was greater than the column statusChangedDate

Steps

  1. Create an expanded version of the tickets table with the assistance of a custom Power Query function (below).

Power Query: Table FactJiraExpanded

let
    Source = FactJira,
    #"Invoked Custom Function" = Table.AddColumn(Source, "ExpandDates", each ExpandDates([creation], [statusChangedDate])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"creation", "statusChangedDate"}),
    #"Expanded ExpandDates" = Table.ExpandListColumn(#"Removed Columns", "ExpandDates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded ExpandDates",{{"ExpandDates", type date}})
    in #"Changed Type"

PowerQuery: Function ExpandDates

(StartDate as date, EndDate as date) =>

let 
DateList= {Number.From(StartDate)..Number.From(EndDate)},
TransformList = List.Transform(DateList,each Date.From(_))

in TransformList
  1. Create a DAX Calendar Table
Calendar =
ADDCOLUMNS (
    CALENDAR (
        MIN ( FactJiraExpanded[ExpandDates] ),
        MAX ( FactJiraExpanded[ExpandDates] )
    ),
    "Month", MONTH ( [Date] ),
    "MonthYear", FORMAT ( [Date], "mmm-yy" ),
    "MontYearIndex", FORMAT ( [Date], "yyyymm" ),
    "Year", YEAR ( [Date] )
)
  1. Create your DAX measure to count the different statuses:
Number of Tickets = DISTINCTCOUNT(FactJiraExpanded[idissue])

Output

enter image description here

Upvotes: 0

Aqshat
Aqshat

Reputation: 77

Based on your approches,why not try the below approach to count the opened ticket in a specific date range.

CountOpenedTickets = 
VAR StartDate = DATE(2024, 6, 1)
VAR EndDate = EOMONTH(StartDate, 0)   

RETURN
CALCULATE(
    DISTINCTCOUNT(Fac_Jira[IdIssue]),
    FILTER(
        Fac_Jira,
        Fac_Jira[CreationDate] <= EndDate &&  // Ticket must be created before or on EndDate
        Fac_Jira[StatusChangedDate] > StartDate &&  // Ticket must still be opened after StartDate
        Fac_Jira[Status] IN {"Opened", "prepared"}  // Only count tickets that are opened
    )
)

Upvotes: 1

Related Questions