Cummulative sum with slicer, big table

[Brise]

Hi, can you help me with this issue?

I have a big table (1600 millions of row) connect to power query with some slicers for consulting of a little part of this table and extract a "mini table" with the information, that can be download later. The slicers are Date, ID and Hour. The table is called log

For the slicers I have three tables related: Fecha (date) related with log[FechaDate] (Date of FechaMov.),  ID related with POS, Hour related with log[Hora](Hour of Hora Mov.). I need calculate the cummulative sum of Monto after the filter by the slicers. 

I think something like this:

SaldoAcumulado =

VAR Maxima_Fecha = MAX(FechaInicio[Date])+time(max(Horas[Value]),59,59)

RETURN

CALCULATE(

    SUM('log'[Monto]),

    FILTER(

        ALLSELECTED('log'),

        'log'[Fecha Mov.] <= Maxima_Fecha

    )

)

But I think that AllSelect disables the slicer filters and when I add the measure to table, the table don't load (exceed the resourses). 

Is there any way to make this measure efficient?

[v-echaithra]

Hi @Brise ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[v-echaithra]

Hi @Brise ,

I understand that you want to show cumulative Monto by datetime (Fecha + Hora).
Try with this DAX measure

Saldoacumulado_optimizado =
VAR Selecteddate = MAX('Fecha'[Date])
VAR Selectedtime = MAX('Horas'[Value])
VAR MaxDatetime = Selecteddate + Selectedtime

RETURN
CALCULATE(SUM('log'[Monto]),
FILTER(KEEPFILTERS('log'), 'log'[Fecha Date] + 'log'[Hora Mov.] <= MaxDatetime))

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra E

[Brise]

I tried to change some things but I still have the problem.

I add Related Tables form minute and hou, for test, and create this measure that work in a visual card:

However in table (right table) is loading for many minutes and so on i have got the error: "limit resourses exceed"

This is the measure:

SaldoAcumulado =

CALCULATE(

    SUM('log'[Monto]),

    ALLEXCEPT('log', 'log'[POS], 'log'[Fecha Date], 'log'[Hora Mov.] ),

    'log'[Hora Mov.] <= time(9,0,0)

    )

The time(9,0,0) and the litle windows of hour minute and seconds was for testing but nothing yet 😞

 

[danextian]

Hi @Brise 

Here's what I would:

1. Split the datetime into separate date and time components to reduce cardinality and create a smaller footprint.
2. Apply the cumulative calculation to a column with low cardinality. Doing this on a column with high distinct values, like Fecha Mov, is not ideal. Referring to the first point, use separate date and time/hour dimensions, relate them to your fact table, and apply the cumulative calculation to those columns.

Cumulative Sum = 
CALCULATE (
    [Sum of Values],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) ),
    FILTER ( ALL ( 'Hours' ), Hours[Hour] <= MAX ( 'Hours'[Hour] ) )
)

Please see the attached sample pbix.

[Brise]

Not working , because this filter all and I need filter all inside the context of slice filtering

[DataNinja777]

Hi @Brise ,

It looks like you have a Power BI table with slicers filtering by Date, ID, and Hour, and you need to calculate a cumulative sum of Monto while respecting these slicers. Given that your dataset contains 1.6 billion rows, optimizing this calculation is crucial to avoid performance issues.

Your current approach using ALLSELECTED may be causing unnecessary memory consumption and performance bottlenecks because it expands the dataset beyond the slicer filters. Instead, using KEEPFILTERS ensures that the slicer selections remain active while applying the cumulative sum logic.

Here’s an optimized DAX measure that calculates the cumulative sum efficiently:

SaldoAcumulado =
VAR MaxFecha =
    MAX ( Fecha[Date] ) + TIME ( MAX ( Horas[Value] ), 59, 59 )

RETURN
CALCULATE (
    SUM ( 'log'[Monto] ),
    KEEPFILTERS ( 'log'[Fecha Mov.] <= MaxFecha )
)

This measure first determines the maximum selected date and hour and then calculates the sum while keeping all slicer-applied filters intact. Since your table includes transactions with timestamps down to seconds, ensuring that the Hora Mov. field is appropriately sorted will help maintain accuracy in the cumulative calculation.

The slicers in your report, as seen in the screenshot, filter transactions by date, hour range, and POS ID. The table visual then displays POS, Fecha Mov., Hora Mov., and Monto. The cumulative sum should be computed dynamically based on these slicer selections, ensuring that it only sums the filtered transactions.

To further improve performance, consider:

1. Indexing the log table in your data source if using SQL.
2. Pre-aggregating sums by Date & ID in Power Query before loading into Power BI.
3. Reducing cardinality in Horas[Value] and Fecha[Date] columns to improve query efficiency.

Try this approach, and let me know how it performs in your report!

Best regards,

[Brise]

Not work 😞

The idea is that the value was $0 , $1.231.582, $1.241.982