cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

It doesn't cumulate at all!

2019-01-11 10_24_49-dev - Power BI Desktop.png

 

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 : 

2019-01-11 10_33_00-dev - Power BI Desktop.png

 

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é").

6 REPLIES 6
Highlighted
Super User IV
Super User IV

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    )
)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

Highlighted
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted

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.

Highlighted

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"

Highlighted

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors