DAX Formula for Cumulative Sum of Count of Tickets

Formula

To calculate the cumulative sum of the count of tickets, you can use the following DAX formula:

CumulativeTickets = 
CALCULATE(
    COUNT(Tickets[TicketID]),
    FILTER(
        ALLSELECTED(Tickets[Date]),
        Tickets[Date] <= MAX(Tickets[Date])
    )
)

Explanation

Components of the Formula

1. CALCULATE: This function changes the context in which data is evaluated. It is essential to perform context transitions and aggregate functions.

2. COUNT(Tickets[TicketID]): This counts the total number of tickets based on the unique identifier for tickets (TicketID). You may need to adjust this based on your specific column names.

3. FILTER(ALLSELECTED(Tickets[Date]), ...):

   • ALLSELECTED: This function returns all the values of the specified column (Tickets[Date]) while retaining any filters applied.
   • The filter condition Tickets[Date] <= MAX(Tickets[Date]) ensures that we are accumulating the counts of tickets up to the current period in the context.

4. MAX(Tickets[Date]): This retrieves the maximum date in the current filter context, ensuring that the cumulative count updates correctly as the context changes (e.g., by month or year).

Logic of Cumulative Sum

• The formula computes the total count of tickets for each date and for each group of filters applied. By employing the FILTER function, the formula constructs a dynamic range of dates up to the current date, thus yielding a cumulative total.

Practical Example

Scenario

Assume you have a sales dataset in the Tickets table that includes:

• TicketID: Unique identifier for each ticket.
• Date: Date on which the ticket was created.

Application

1. Create a Measure: Use the provided DAX formula above to create a measure in your Power BI or analysis model.

2. Add to Report:

   • Place the Date field on the X-axis of a line chart.
   • Add the CumulativeTickets measure to the values area.

3. Result: The chart will display a cumulative count of tickets generated over time, allowing for an analysis of trends and performance.

Conclusion

This DAX formula effectively captures the cumulative count of tickets based on their creation date. It provides clarity on ticket trends and can be adapted for various reporting applications. For further mastery of DAX, consider exploring additional resources available on the Enterprise DNA platform.