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 forecast is the same as inventory, otherwise add previous week inventory with forecast data to create a new forecast. I tried Max, earlier, coalesce, but all failed. even saw an example with ALLEXCEPT, but it didnt work. Please help. Sorry, i am new to Power BI. Below is an example of how it is power bi and how i want it in excel highlighted in yellow.

 

 

 

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)

     

10 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    1 year ago

    Hi arun0386 ,

    This is a unique challenge... but... possible...

    I'll break it down into three separate measures:

    First step is to fill down:

    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
    CALCULATE(SUM('Table'[Inventory]), KEEPFILTERS('Table'[Week] = _LastWeek), ALL('Table')), 
    // if there is, return me normal inventory value
    SUM('Table'[Inventory])
)


    Second step is to generate the cumulative total on forecast values: 

    Step.02.Window = 
VAR _CurrWeek = SELECTEDVALUE('Table'[Week])

RETURN

SUMX(
    // peer into the previous row context
    WINDOW(-1, REL, 0, REL, VALUES('Table'[Week]),,,PARTITIONBY('Table'[Week])), 

    // generate my cumulative total
    CALCULATE(SUM('Table'[Measure]), 'Table'[Inventory] = BLANK(), ALL('Table'), KEEPFILTERS('Table'[Week] <= _CurrWeek))
)


    Last step is to add them all together:

    Step.03.Final = [Step.01.DrillDown] + [Step.02.Window] // combine it all together

     

    And the final results:

     

     

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

    Cant seem to access the pbix file. Can you re-share please? and thanks a lot again.

    • hnguy71's avatar
      hnguy71
      Super User
      1 year ago

      I've added a secondary link. you can try again.