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

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

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
Helper II
Helper II

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

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

Highlighted
Community Support
Community Support

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

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
Helper II
Helper II

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

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
Helper II
Helper II

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

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
Community Support
Community Support

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

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors