What's wrong with my cumulative / running total column?

[6mon]

It doesn't cumulate at all!

COST_PERIOD_COL is a column containing costs. Since the model is a periodic snapshot, it's normal there are voids.

COST_CUM is a measure that gives the expected results but it's slow so I'm aiming to use a column instead. It definition is : 

COST_CUM:= 
IF(
    [PROJECT_IS_ONGOING];
    SUMX(
        FILTER(
            ALL(DIM_DATE[THE_DATE]);
            DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE])
        );
        FIRSTNONBLANK(FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL];1)
    )
)

COST_CUM_COL is the goal. It's defined as :

CALCULATE (
    SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] );
    FILTER(
        ALL ( DIM_DATE ) ;
        DIM_DATE[THE_DATE] <=  MAX( DIM_DATE[THE_DATE] )
    )
)

but doesn't cumulate. Instead it gives the same single value as the column it's reading from.

Another approach (using the fact table instead of the date dimension) gives a very different (but also wrong) result : 

This time COST_CUM_COL is defined as :

CALCULATE (
    SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] );
    FILTER(
        ALL ( FACT_COST_RESOURCE_CATEGORY ) ;
        FACT_COST_RESOURCE_CATEGORY[THE_DATE] <=  MAX( FACT_COST_RESOURCE_CATEGORY[THE_DATE] )
    )
)

I've tried many permutations with or without FILTER, using ALLSELECTED with the tables and columns, EARLIER instead of MAX, etc. 

Model has 4 dimensions : DATE, PROJECT (redacted in the screenshots), ACTIVITY ("Achat petits outils" in my example) and RESOURCE_CATEGORY ("Équipement" and "Indéterminé").

[v-shex-msft]

HI @6mon,

For cumulative calculate, I think you need to add all/allselected function on fact table to break current filter effect and use calendar date as filter condition:

Column =
VAR currDate =
    MAX ( Dim_date[Date] )
RETURN
    CALCULATE (
        SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] );
        FILTER (
            ALLSELECTED ( FACT_COST_RESOURCE_CATEGORY );
            FACT_COST_RESOURCE_CATEGORY[THE_DATE] <= currDate
        );
        VALUES ( PROJECT[Projecct] )
    )

If above not help, can you please share a pbix file with some sample data for test and coding formula?

Regards,

Xiaoxin Sheng

[6mon]

Unfortulately this formula brings me back to 'scenario B' in which a single (total?) value is repeated, across all 4 dimensions, including time. There is no cumulation per se. 

I'm begining to believe there's something wrong with model/joins. All the "ID_DIM_*" columns I use in the formulas belong to the fact table (they're the FOREIGN KEYS), should it be otherwise?

The model couldn't be simpler : one fact table that contains a couple of measures and FK to 4 dimensions, one entry per day, and per each dimension (for the days the project is active) in the fact table.

[6mon]

Here's a file containing a representative example : https://totalprojectlogistics-my.sharepoint.com/:u:/g/personal/sbenoit_tpl-solutions_com/Ea_Q4wIJDLF...

I Expect COST_CUM to give 0, 2 and 202 for "Ress a" and 0, 5 and 6 for "Ress b"

[v-shex-msft]

Hi @6mon,

You can try to use following formula if it works, I add all columns which you used in matrix visual as conditions:

COST_CUM =
CALCULATE (
    SUM ( FACT[COST] ),
    FILTER (
        ALLSELECTED ( 'FACT' ),
        [ID_DIM_PROJECT] = EARLIER ( 'FACT'[ID_DIM_PROJECT] )
            && [ID_DIM_RESS_CAT] = EARLIER ( 'FACT'[ID_DIM_RESS_CAT] )
            && [ID_DIM_ACTIVITY] = EARLIER ( [ID_DIM_ACTIVITY] )
            && FACT[THE_DATE] <= EARLIER ( 'FACT'[THE_DATE] )
    )
)

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Perhaps try:

Column = 
VAR __date = [THE DATE]
RETURN
CALCULATE (
    SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] );
    FILTER(
        ALL ( DIM_DATE ) ;
        DIM_DATE[THE_DATE] <=  __date    )
)

[6mon]

No dice, still only gives the period's value 😞