March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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:
When I do this in Power BI, I get a circular reference. I can't work out how to implement this, please help!
Solved! Go to 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])
Proud to be a 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
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!
Unfortunately not.
This is what I am expecting:
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])
Proud to be a Super User!
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
@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!
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
Proud to be a Super User!
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?
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!
@Anonymous , Try a new column like
new column =
var _max = [Week end] - 7
return
maxx(filter(Table, [Week end] = _max),[SOH]) + [IN] - [OUT]
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |