Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
6mon
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
v-shex-msft
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.

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.

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"

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.
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.