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)

     

arun0386's avatar
arun0386
Frequent Visitor
1 year ago

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's avatar
    hnguy71
    Super User
    1 year ago

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