Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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!

2 ACCEPTED SOLUTIONS

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
ryan_mayu
Super User
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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Unfortunately not.

This is what I am expecting:

david_michell_0-1613622525540.png

David

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

1.PNG





Did I answer your question? Mark my post as a solution!

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:

david_michell_0-1613956792034.png

 

How would I modify the DAX to include that?

 

Thanks

David

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

1.PNG





Did I answer your question? Mark my post as a solution!

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

 

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Vera_33
Resident Rockstar
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!

amitchandak
Super User
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]

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.