Forum Discussion

DataHero's avatar
DataHero
Icon for Helper I rankHelper I
1 year ago

DAX Cumulative Active Count with categories

Hi Forum,

Im stucking with the calculation of Cumulative Active Count of IT tickets (inquiries).
The raw data look like this:

INQUIRY_IDTEAMMinActionDateMaxActionDateIA_ACTION
555555Service01.04.202205.04.2022different Team assigned
555555Support05.04.202209.04.2022different Team assigned
555555Support09.04.202209.04.2022Ticket closed

In this example, the Inquiry was active from 01.04.2022 - 09.04.2022.
If I filter for example Team Support, then it would be 05.04.2022 - 09.04.2022.
So the calculation should be dynamic.

It seems that below measure doesnt work here for the calculation:

VAR CurrentDate = MAX(Dim_Date[Datum])
RETURN
CALCULATE(
DISTINCTCOUNTROWS(Fact_Inquiries),
Fact_Inquiries[MinActionDate] <= CurrentDate, -- Inquiry started on or before CurrentDate
Fact_Inquiries[MaxActionDate] >= CurrentDate -- Inquiry ended on or after CurrentDate
)

Normally, I use this measure often but til now I only had use cases with 1 row per Id.
The result of this measure in the mentioned usecase is extremely high.


Does someone know, how to fix this?
Thanks for any help.

Kind regards,
DataHero

Need Help

6 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    1 year ago

    what is your expected result, please provide what you expect so that its clearly undertood.    are you expecting the answer to be 2 when filter by support?

      • vanessafvg's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        1 year ago

        ah ok, why not using distinctcount on the id instead?  

         

        VAR CurrentDate =
            MAX ( Dim_Date[Datum] )
        RETURN
            CALCULATE (
                DISTINCTCOUNT ( Fact_Inquiries[id] ),
                Fact_Inquiries[MinActionDate] <= CurrentDate,
                -- Inquiry started on or before CurrentDate
                Fact_Inquiries[MaxActionDate] >= CurrentDate -- Inquiry ended on or after CurrentDate
            )

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    1 year ago

    Hi DataHero 
    I don't believe your cummulative total is correct. You're current calculation is trying to return all records within a specific range but what you really want is the unique count of active tickets and doing a rolling sum.

     

    Try this instead:

    Active_Tickets = 
// what is the current date in context? 
VAR _Date = MAX(Dim_Date[Datum])

RETURN

/*
    Only interested in count of active tickets, therefore distinct count of inquiries.
    Remove filter on date table to get all results from all dates in context.
    Add filter for only active tickets for that day
    rolling total for all tickets
*/

CALCULATE( 
    DISTINCTCOUNT(Fact_Inquiries[INQUIRY_ID]), 
    ALL(Dim_Date), 
    Fact_Inquiries[IA_ACTION] <> "TICKET CLOSED", 
    Dim_Date[Datum] <= _Date
)