cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

2 ACCEPTED SOLUTIONS
Super User

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

Proud to be a Super User!

Super User

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

Proud to be a Super User!

10 REPLIES 10
Super User

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

Proud to be a Super User!

Anonymous
Not applicable

Unfortunately not.

This is what I am expecting:

David

Super User

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

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

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

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

@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

Proud to be a Super User!

Resident Rockstar

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
Not applicable

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!

Super User

@Anonymous , Try a new column like

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors