Forum Discussion

arun0386's avatar
arun0386
Frequent Visitor
1 year ago
Solved

Cumulative calculation with Calculated column

Hi, I am trying to do cumulative calculation. I have inventory data till latest week and i have forecast delta for remaining weeks. I need this calculation :  if inventory data is there, then forecas...
Need Help
  • hnguy71's avatar
    hnguy71
    1 year ago

    Hi arun0386 ,
    Now that you're able to retrieve week 34, this code should definitely work:

     

    Step.01.DrillDown = 
VAR _LastWeek = CALCULATE( MAX('Table'[Week]), FILTER( ALL('Table'), NOT(ISBLANK('Table'[Inventory]))) )
RETURN
IF( 
    // checks to see if I have an inventory week
    SELECTEDVALUE('Table'[Week]) > _LastWeek, 
    // if not, let's "fill down" and return the value for the last week
    CALCULATE(SUM('Table'[Inventory]), KEEPFILTERS('Table'[Week] = _LastWeek), ALL('Table')), 
    // if there is, return me normal inventory value
    SUM('Table'[Inventory])
)

     



    Sharing the pbix that I used for your example so you can take a look at it as a reference.

    window-function-previous-row.pbix (link 1)

    window-function-previous-row.pbix (link 2)

     

hnguy71's avatar
hnguy71
Super User
1 year ago

Hi arun0386 ,

This is a unique challenge... but... possible...

I'll break it down into three separate measures:

First step is to fill down:

Step.01.DrillDown = 
VAR _LastWeek = CALCULATE(MAX('Table'[Week]), 'Table'[Inventory] <> BLANK(), ALL('Table'))
RETURN
IF( 
    // checks to see if I have an inventory week
    SELECTEDVALUE('Table'[Week]) > _LastWeek, 
    // if not, let's "fill down" and return the value for the last week
    CALCULATE(SUM('Table'[Inventory]), KEEPFILTERS('Table'[Week] = _LastWeek), ALL('Table')), 
    // if there is, return me normal inventory value
    SUM('Table'[Inventory])
)


Second step is to generate the cumulative total on forecast values: 

Step.02.Window = 
VAR _CurrWeek = SELECTEDVALUE('Table'[Week])

RETURN

SUMX(
    // peer into the previous row context
    WINDOW(-1, REL, 0, REL, VALUES('Table'[Week]),,,PARTITIONBY('Table'[Week])), 

    // generate my cumulative total
    CALCULATE(SUM('Table'[Measure]), 'Table'[Inventory] = BLANK(), ALL('Table'), KEEPFILTERS('Table'[Week] <= _CurrWeek))
)


Last step is to add them all together:

Step.03.Final = [Step.01.DrillDown] + [Step.02.Window] // combine it all together

 

And the final results: