Calculation based on previous period calculation

[Anonymous]

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
29/09/2019		213	151
6/10/2019		166	160
13/10/2019		88	94
20/10/2019		240	118

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!

[ryan_mayu · Accepted solution]

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

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

Unfortunately not.

This is what I am expecting:

David

[ryan_mayu · Accepted solution]

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

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 · Accepted solution]

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

That seems to work.

Just so I understand, what this is doing is summing the INs and OUTs from the last Actual SOH to the current date, and adding to the last actual SOH?

Thanks

David

[ryan_mayu]

@Anonymous 

That based on the excel formula you provided. If we have actual, then take actual. If not, we take the previous actual and add all in from that time and minus all out from that time

[Vera_33]

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]

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!

[amitchandak]

@Anonymous , Try a new column like

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