topic Re: Cumulative total from Matrix column in DAX Commands and Tips

Cumulative total from Matrix column

Anonymous — Tue, 21 Jun 2022 16:09:37 GMT

Dear Guru,

I do struggle with cumulative total of two measures.

My orderbook is in day, forecast in week.

I calculate Order book per week. I calculate Forecast per week. For each week, Demand is the MAX of order and Forecast.

Demand = max([OpenOrder],[Forecast])

Unitl there all is perfect ! Then issue appear. I want to calculate my cumulative Demand.

Demand_Cumulative =

CALCULATE([Demand],

FILTER(ALL('DateTable'),

'DateTable'[Date]<= Max(DateTable[Date]) ) )

Issue is that standard cumulative formula above is "applying" the max between cumulative forecast, and cumulative demand, back to raw data table calculation instead of suming calculated demand of my matrix

Below a table to illustrate the issue :

Current Week	1	2	3	4	5	6	7
Forecast	5	10	5	5	15	10	5
Order Book	30	3	2	0	0	20	0
Demand (=Max Forecast/OrderBook)	30	10	5	5	15	20	5
Expected Cumulative Demand	30	40	45	50	65	85	90
Cumulative Forecast ( I don't want just to illustrate)	5	15	20	25	40	50	55
Cumulative Order ( I don't want just to illustrate)	30	33	35	35	35	55	55
Wrong result by my measure (= max umulative forecast / cumulative demand)	30	33	35	35	40	55	55

Would you have any idea to tackle this point ? how can I do a running total of my matrix Column ?

Thanks a lot !

Re: Cumulative total from Matrix column

Jihwan_Kim — Tue, 21 Jun 2022 17:56:47 GMT

Hi,

I am not sure how your dim DateTable looks like, but please try something like below.

Demand_Cumulative = SUMX ( FILTER ( ADDCOLUMNS ( ALL ( 'DateTable'[yearweeknumbercolumn] ), "@demand", [Demand] ), 'DateTable'[yearweeknumbercolumn] <= MAX ( 'DateTable'[yearweeknumbercolumn] ) ), [@demand] )

Re: Cumulative total from Matrix column

Anonymous — Wed, 22 Jun 2022 07:39:14 GMT

Hello !

First thanks a lot for answering me, at least I feel less alone in front of my issue !

My date table is a table with all dates, and I got many columns to get week number, fiscal period, in the past etc... So I got a column WeeknYear that can match your yearweeknumbercolumn.

Demand_Cumulative =

SUMX (

FILTER (

ADDCOLUMNS ( ALL ( 'DateTable'[WeeknYear] ), "@demand", [Demand] ),

'DateTable'[WeeknYear] <= MAX ( 'DateTable'[WeeknYear] )

[@demand]

)

Unfortunately my exemple is for one sku one country. In my data base I got thousnads of SKU for dozaine of countries, the formula is running very long time and results is even worse : I assume it is because it cannot creates thousands of columns in the datetable for each week to create the demand per each SKU and country.

Result I got is for each week, demand cumulative is only the max of each week between order and forecast (not suming cumulative at all)

Current Week	1	2	3	4	5	6	7
Forecast	5	10	5	5	15	10	5
Order Book	30	3	2	0	0	20	0
Demand cumulative wrong	30	10	5	5	15	20	5

Re: Cumulative total from Matrix column

Jihwan_Kim — Thu, 23 Jun 2022 03:38:26 GMT

Hi,

I am not sure but I assume your 'DateTable'[WeeknYear] column is sorted by other column. In this case, the ALL function needs to be applied to the sort column as well.

Please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

Thanks.

Re: Cumulative total from Matrix column

Anonymous — Thu, 23 Jun 2022 07:26:23 GMT

Hello !

Thanks a lot for feedback ! Finally I did came out talking to some contact I have found internally that is not far from yours, except that I cannot addcolumn due to differente dimensions in between tables.

One of the issue is that my data table is sorted by day. Orders are daily, while forecast is weekly conentrated on one day. Then suming cumulative looking at day bucket Demand = Max ORder/Forecast, was not correct. I did add a column to put all orders to last day of the week, same day than Forecast, and use this one as relation with date table. My orders become weekly, on the same day than forecast.

On top, using as you advice SUMX and not CALCULATE was the solution. Below works finally like a charm !

Demand_Cumulative =

SUMX (

FILTER (

ALLSELECTED ( DateTable[Date] ),

DateTable[Date] <= MAX ( DateTable[Date] )

[Demand]

)

Hope can helps others on this forum !