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

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks ryan_mayu 

I have been given a further complication, where I need to include a state:

 

How would I modify the DAX to include that?

 

Thanks

David

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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])