Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative total from Matrix column

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 Week1234567
Forecast5105515105
Order Book303200200
Demand (=Max Forecast/OrderBook)30105515205
Expected Cumulative Demand30404550658590
Cumulative Forecast ( I don't want just to illustrate)5152025405055
Cumulative Order ( I don't want just to illustrate)30333535355555
Wrong result by my measure (= max umulative forecast / cumulative demand)30333535405555

 

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

 

Thanks a lot !

  • Anonymous's avatar
    Anonymous
    4 years ago

    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 !

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 Week1234567
      Forecast5105515105
      Order Book303200200
      Demand cumulative wrong30105515205
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        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.