Forum Discussion
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...
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)
Any calculation other than a hardcoded numeric value isnt populating. LOL. FYI, inventory is a column in the table. rest everything is a measure.
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)