Forum Discussion
Cumulative calculation with Calculated column
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)
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
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.