Cumulative Total with two columns

[Glaeran]

Hello Everyone,

I'm dealing with following scenario.

We have table with following structure:

What I would like to do is:

Calculate accumulative total for POS_AMT measure based on WEEK_END_DATE.

I did that using following formula:

POS_AMT_CUMULATIVE = 
CALCULATE(
	SUM(ARD_POS_FRCST_WKLY[POS_AMT]);
	FILTER(
		ALLEXCEPT(ARD_POS_FRCST_WKLY; ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME];ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]);
		ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX(ARD_POS_FRCST_WKLY[WEEK_END_DATE])
	)
)

However, at certain point, POS_AMT values for few weeks are Blanks, with just FORECAST_AMT column being filed.

What I would want to achieve is -> For WEEK_END_DATE values that have FORECAST_AMT, but doesn't have POS_AMT use FORECAST_AMT insead of POS_AMT in cumulative calculation.

The other way would be to calculate cumulative POS_AMT for only those weeks that have such values and later on calculate another measure with FORECAST_AMT, but only for those weeks that doesn't have POS_AMT.

Hope it's clear enough... 🙂

[v-jiascu-msft]

@Glaeran

Hi, 

You just need to change SUM into SUMX. Try this one below.

POS_AMT_CUMULATIVE =

CALCULATE (

    SUMX (

        ARD_POS_FRCST_WKLY,

        IF (

            ARD_POS_FRCST_WKLY[POS_AMT] = 0,

            ARD_POS_FRCST_WKLY[FORECAST_AMT],

            ARD_POS_FRCST_WKLY[POS_AMT]

        )

    ),

    FILTER (

        ALLEXCEPT (

            ARD_POS_FRCST_WKLY,

            ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME],

            ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]

        ),

        ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

    )

)

[Glaeran]

Thank you!

That actually is working almost as expected.

"Almost", because our "ideal" scenario, would be to have those values that are using [FORECAST_AMT} column to be colored separately. Is it doable though?

[v-jiascu-msft]

@Glaeran

Hi,

We can’t color them separately due to we accumulate the total. If the first  month uses FROCAST_AMT, we can say the last month uses it too. If we want to find out the current month using it or not, we can add a calculated column, and then add it to legend. This doesn’t work with area chart. You can have a look at the image below. The black column means this column uses FROCAST_AMT. Is this what you want?

UsingFrocast =

CALCULATE (

    IF ( MIN ( ARD_POS_FRCST_WKLY[POS_AMT] ) = 0, 1, 0 ),

    FILTER (

        ARD_POS_FRCST_WKLY,

        MONTH ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

            = MONTH ( EARLIER ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ) )

    )

)

Best regards

Dale

[Glaeran]

@v-jiascu-msftUnfortunately not :(.

Our goal is to show both accumulative POS_AMT and FORECAST_AMT on 1 chart (preferably Area Chart or Line Chart), with the same idea as before so:

If WEEK_END_DATE has POS_AMT show POS_AMT otherwise show FORECAST_AMT, or by any chance "mark" the week in which we're starting to use FORECAST_AMT.

[v-jiascu-msft · Accepted solution]

@Glaeran

Hi,

This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

Column =
IF (
    ARD_POS_FRCST_WKLY[WEEK_END_DATE]
        = CALCULATE (
            MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
            FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
        ),
    1,
    0
)

Best Regards!

Dale