Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculation based on previous period calculation

I have some weekly stock data, that looks like this 

Week endActual SOHINOUT
1/09/20194992218100
8/09/2019 22135
15/09/2019 200113
22/09/20195050246128
29/09/2019 213151
6/10/2019 166160
13/10/2019 8894
20/10/2019 240118

 

I want to add a calculated column to determine the Stock on hand, where the calculation is:

SOH (last week) + IN - OUT

 

I only have Actual SOH information for some periods, so in that case, I need to use the calculated SOH number instead of actual, using the formula (from excel):

CALC SOH =

IF(ISBLANK(Actual SOH (LW)),

CALC SOH (LW) + IN - OUT,

ACTUAL SOH (LW) + IN - OUT

 

My Calculated column for SOH (LW) is:

SOH LW =
LOOKUPVALUE(
SOH[CALC SOH],
SOH[Week-end],
DATEADD(SOH[Week-end],-7,DAY)
)
 

When I do this in Power BI, I get a circular reference. I can't work out how to implement this, please help!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    updated the DAX based on your expected result.

    Column = 
VAR lastsohdate=maxx(FILTER('Table','Table'[Week end]<EARLIER('Table'[Week end])&&NOT(ISBLANK('Table'[Actual SOH]))),'Table'[Week end])
VAR lastsoh=maxx(FILTER('Table','Table'[Week end]=lastsohdate),'Table'[Actual SOH])
return if(ISBLANK('Table'[Actual SOH]),lastsoh+sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>lastsohdate),'Table'[IN])-sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>lastsohdate),'Table'[OUT]),'Table'[Actual SOH])

  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    sry for the late response, i've been quite busy these days.

    Column = 
VAR lastsohdate=MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[ACTUAL SOH]<>0&&'Table'[State]=EARLIER('Table'[State])),'Table'[Date])
VAR lastsoh=maxx(FILTER('Table','Table'[Date]=lastsohdate&&'Table'[State]=EARLIER('Table'[State])),'Table'[Actual SOH])
return if(ISBLANK('Table'[Actual SOH]),lastsoh+sumx(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Date]>lastsohdate&&'Table'[State]=EARLIER('Table'[State])),'Table'[IN])-sumx(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&'Table'[Date]>lastsohdate&&'Table'[State]=EARLIER('Table'[State])),'Table'[OUT]),'Table'[Actual SOH])

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a new column like

     

    new column =
    var _max = [Week end] - 7
    return
    maxx(filter(Table, [Week end] = _max),[SOH]) + [IN] - [OUT]

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    How do you calculate your SOH for the first row without SOH LW and Actual LW? Can you provide the expected SOH column in above sample data? Are you looking for a solution in M or DAX?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      These would be my expected values.

      1/09/19 - 4992

      8/9/19 - 4879

      15/9/19 - 4966

      22/9/19 - 5050 (same as actual)

      29/9/19 - 5112

      6/10/19 - 5118

       

      I tried to put a table in, but kept getting an error.

       

      If there is an actual SOH, that becomes the CALC SOH.

      I'm looking for a DAX Solution, as I'm coming from a calculated table, and need a result for every week, even when the base data is not there.

       

      Thanks!

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    is this what you want?

    Column = 
VAR lastsohdate=maxx(FILTER('Table','Table'[Week end]<EARLIER('Table'[Week end])&&NOT(ISBLANK('Table'[Actual SOH]))),'Table'[Week end])
VAR lastsoh=maxx(FILTER('Table','Table'[Week end]=lastsohdate),'Table'[Actual SOH])
return if(ISBLANK('Table'[Actual SOH]),lastsoh+sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>=lastsohdate),'Table'[IN])-sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>=lastsohdate),'Table'[OUT]),'Table'[Actual SOH]+'Table'[IN]-'Table'[OUT])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Unfortunately not.

      This is what I am expecting:

      David

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous 

        updated the DAX based on your expected result.

        Column = 
VAR lastsohdate=maxx(FILTER('Table','Table'[Week end]<EARLIER('Table'[Week end])&&NOT(ISBLANK('Table'[Actual SOH]))),'Table'[Week end])
VAR lastsoh=maxx(FILTER('Table','Table'[Week end]=lastsohdate),'Table'[Actual SOH])
return if(ISBLANK('Table'[Actual SOH]),lastsoh+sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>lastsohdate),'Table'[IN])-sumx(FILTER('Table','Table'[Week end]<=EARLIER('Table'[Week end])&&'Table'[Week end]>lastsohdate),'Table'[OUT]),'Table'[Actual SOH])