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

From the first query

From the adjusted one

 

  • 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

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