Cumulative calculation with Calculated column

[arun0386]

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 forecast is the same as inventory, otherwise add previous week inventory with forecast data to create a new forecast. I tried Max, earlier, coalesce, but all failed. even saw an example with ALLEXCEPT, but it didnt work. Please help. Sorry, i am new to Power BI. Below is an example of how it is power bi and how i want it in excel highlighted in yellow.

[arun0386]

Cant seem to access the pbix file. Can you re-share please? and thanks a lot again.

[hnguy71]

I've added a secondary link. you can try again.

[arun0386]

From the first query

From the adjusted one

[hnguy71]

Hi @arun0386 

Looks like we found your issue. Seems it's attempting to pick up week 53 but your maximum week it should pick up is week 34. 

I've modified the last week variable a bit. Hopefully this should return the desired week 34.

Send me a screenshot of the output

Step.01.DrillDown =  
VAR _LastWeek = CALCULATE( MAX('Table'[Week]), FILTER( ALL('Table'), NOT(ISBLANK('Table'[Inventory]))) )

RETURN

_LastWeek

[arun0386]

Any calculation other than a hardcoded numeric value isnt populating. LOL. FYI, inventory is a column in the table. rest everything is a measure.

[hnguy71 · Accepted solution]

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)

[arun0386]

The revised query is giving 34 as the latest week. And i also tried adding the query line from Message 4 to get 100 in blank rows; it worked; however, when i changed it to the query line from your first response, it doesnt fill down.

[arun0386]

Hi @hnguy71 , Thanks for responding. Step 2 & 3 worked like a charm. But something is not working in Step 1. it is not filling up the following weeks like your screenshot. mystery!!!

[hnguy71]

Hi @arun0386 

Glad it's halfway working for ya! We'll need to debug as to understand why the first measure is not filling down. Let's modify the first measure twice and see what we return.

Adjust to this expression and share me the screenshot of the result:

Step.01.DrillDown = 
VAR _LastWeek = CALCULATE(MAX('Table'[Week]), 'Table'[Inventory] <> BLANK(), ALL('Table'))
RETURN
_LastWeek & " | " & SELECTEDVALUE('Table'[Week])

Then, adjust the expression again and share me the screenshot of the next result:

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
    100, 
    // if there is, return me normal inventory value
    SUM('Table'[Inventory])
)

[hnguy71]

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: