Cumulative count accross selected period

[EugenioProlog]

I have this metric:

Qtd Reuniões | Tickets =

CALCULATE(

    COUNTROWS(fct__comercial__atividades__reunioes),

    FILTER(

        fct__comercial__atividades__reunioes,

        fct__comercial__atividades__reunioes[atividade_id] IN

        SELECTCOLUMNS(

            fct__comercial__atividades__reunioes__vinculo__tickets,

            "atividade_id", fct__comercial__atividades__reunioes__vinculo__tickets[atividade_id]

        )

    )

)
that results in this bar chart:

How can I adapt this metric in order to show the cumulative count through the selected period, instead of displaying the individual count of each day?

Thank you!

[srlabhe · Accepted solution]

To adapt your DAX metric to show the cumulative count over a selected period, you will need to add a CALCULATE function that modifies the filter context of your COUNTROWS function. This is a common pattern for calculating running totals over time in Power BI. 

The standard approach is to use CALCULATE with a FILTER function that uses a date table to expand the filter context. The FILTER will iterate through all relevant dates up to the latest date in the current context, and the COUNTROWS function will be calculated for each day. 

Here is a step-by-step breakdown of how to modify your DAX measure.

Step 1: Create a Date table

For any time-intelligence calculation like a cumulative total, you need a dedicated and marked Date table in your model. If you do not have one, you can create one using the CALENDAR or CALENDARAUTO DAX functions. 

Example DAX for a Date table:

dax

DateTable = CALENDARAUTO()

Make sure this new DateTable is marked as a date table in Power BI and has a relationship to your fct__comercial__atividades__reunioes table on a date column. 

Step 2: Create the base measure

The core of your calculation is the count of meetings linked to tickets. It is good practice to first define this as a base measure. 

dax

Qtd Reuniões com Tickets = 
CALCULATE(
    COUNTROWS( fct__comercial__atividades__reunioes ),
    FILTER(
        fct__comercial__atividades__reunioes,
        fct__comercial__atividades__reunioes[atividade_id] IN VALUES( fct__comercial__atividades__reunioes__vinculo__tickets[atividade_id] )
    )
)

• VALUES( fct__comercial__atividades__reunioes__vinculo__tickets[atividade_id] ) is used to get the list of relevant atividade_id values more efficiently than SELECTCOLUMNS.

Step 3: Create the cumulative measure

Next, you can create the cumulative version of the measure using your base measure. 

dax

Qtd Reuniões | Tickets Acumulado =
CALCULATE(
    [Qtd Reuniões com Tickets],
    FILTER(
        ALLSELECTED( DateTable ),
        DateTable[Date] <= MAX( DateTable[Date] )
    )
)

How the cumulative measure works:

• [Qtd Reuniões com Tickets] is your base measure defined in Step 2.
• ALLSELECTED( DateTable ) removes any filters applied to the DateTable but respects slicers on other tables. This creates a virtual table with all the dates selected in your slicers or visual filters.
• FILTER(...) iterates through that virtual date table.
• DateTable[Date] <= MAX( DateTable[Date] ) is the key for the running total. MAX( DateTable[Date] ) gets the latest date in the current context (e.g., the specific date for that row in your visual). The filter then includes all dates from the selected period that are less than or equal to that date.
• The CALCULATE function then re-evaluates your base measure for each date, with the filter context expanded to include the entire range of dates up to the current one. 

Usage in visuals

To visualize the cumulative count, use this new measure in a chart (like a line or bar chart) and add the date from your Date table to the visual's axis. This will produce a running total that updates based on the period you select.