cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative Total with two columns

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... 🙂

1 ACCEPTED SOLUTION Microsoft

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5 Microsoft

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

)

)

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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? Microsoft

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

@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. Microsoft

@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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   