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 end Actual SOH IN OUT 1/09/2019 4992 218 100 8/09/2019   22 135 15/09/2019   200 113 22/09/2019 5050 246 128 ...
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])

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!