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 

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's avatar
arun0386
Frequent Visitor
1 year ago

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's avatar
    hnguy71
    Super User
    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)