Rolling sum performance on based on second date column

[Anonymous]

I have a cash flow table with the characteristics:

• cob_date: date that the data is generated for
• cash_flow_date: the date for which the cash flow is produced for
• amount: the cash flow amount
• various other FKs to dimension tables

I have a measure that performs a rolling sum of the [amount] based on the rolling period of [cash_flow_date]. This measure is also meant to retain any context from the [cob_date] if available.

It is producing the numbers correctly. My concern is that the performance degrades (somewhat linearly) the more dates are included in the [cash_flow_date] context. The degradation also becomes quite material in some use cases (>1.5s on common date range filters). Can anyone provide some insights to this?

For comparison I have similar measures on other fact tables that perform a rolling sum scales up with very minimal performance degradation. I have also tested performing the rolling sum based on [cob_date] and this also gives consistently good performance no matter how many dates are in the context which is odd as there are technically more rows considered in this scenario than my initial case.

Looking at the server timings, I can see that the rolling sum over [cash_flow_date] does proportionally consume more FE than when rolling over [cob_date]. In saying that the rolling sum over [cash_flow_date] is producing a CallbackDataID which am not sure why.

This is my measure:

VAR _max_flow_date = MAX('t_fact_cash_flow'[cash_flow_date])
RETURN
CALCULATE(
    SUM('t_fact_cash_flow'[contractual_amount])
    ,FILTER(
        DISTINCT(ALLSELECTED('t_fact_cash_flow'[cash_flow_date]))
        ,'t_fact_cash_flow'[cash_flow_date] <= _max_flow_date
    )
)

[Anonymous]

Yeah... My advice is this: Build a correct star-schema model with proper date tables. If you want to know why, there are YT videos by Alberto Ferrari that explain why that should be. Great chances are that when you start doing things as Best Practices dictate, you won't have any issues even on very big models.

[Anonymous]

I have also tested remaking the measure by referencing a date table in a relationship with the [cash_flow_date] column. Unfortunately still resulting in bad performance.