Forum Discussion
Helper IDynamically referencing above rows in the same column
NaveenMD
Here you goClosing Stock 2 = VAR T1 = FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) RETURN SUMX ( T1, VAR Qty = 'Table'[Material QTY] VAR T2 = FILTER ( T1, 'Table'[Index] >= EARLIER ( 'Table'[Index] ) ) VAR Rate = PRODUCTX ( T2, IF ( 'Table'[Index] = EARLIEST ( 'Table'[Index] ), 1, ( 1 - 'Table'[Rate] ) ) ) RETURN Qty * Rate )
Community ChampionNaveenMD See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Community ChampionNaveenMD
There is no fixed pattern for what I call "Semi-Recursive" calculations.
Referring to the previous row in the same column that is under evalution is not possible in Power Bi as the whole column is evaluated as set not cell by cell as the case in excel. However, we can trace back the the calculation of the previous cell to notice that it is actually evaluated the existing values of other (existing) columns and the cell previous to it and so on until we reach to the very first cell which is being totally evaluated from other existing column(s).
This would require taking some advantage of mathmics. Assuming quantity column "Q" and (1 - [Ratio]) is R then we can write
Row1:
= Q1
Row2:
= Q1*R2 + Q2
Row3:
= (Row2)*R3 + Q3
= (Q1*R2 + Q2)*R3 + Q3
= Q1*R2*R3 + Q2*R3 + Q4
Similarly Row4 would be:
= Q1*R2*R3*R4 + Q2*R3*R4 + Q3*R4 + Q4
And so on...If we look closely to Row4
Now it is a matter to find the tables that need to be iterated inside each of these two iterators. The rest is just fine tuning some details.
Closing Stock 2 =
VAR MinIndex = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Index] )
VAR T1 = FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
RETURN
SUMX (
T1,
VAR Qty = 'Table'[Material QTY]
VAR T2 = FILTER ( T1, 'Table'[Index] > EARLIER ( 'Table'[Index] ) )
VAR Rate =
COALESCE(
PRODUCTX (
T2,
1 - 'Table'[Rate]
),
1
)
RETURN
Qty * Rate
)
NaveenMDtamerj1 this is where I am facing an issue by using the DAX
column 3 is the required rate% and column 2 is the actual rate % that has been calculated using the DAX.
I am hypothesising that somewhere the rate values are getting averaged out thus we are facing this problem.
Material QTY Rate Date Correct Closing stock Index 0 0.2 11-Apr-23 0 0 0 0.2 12-Apr-23 0 1 0 0.2 13-Apr-23 0 2 0 0.2 14-Apr-23 0 3 0 0.2 15-Apr-23 0 4 0 0.2 16-Apr-23 0 5 150000 0.2 17-Apr-23 150000 6 1000 0.2 18-Apr-23 121000 7 0 0.25 19-Apr-23 90750 8 100000 0.15 20-Apr-23 168062.5 9 0 0.12 21-Apr-23 147895 10 0 0.3 22-Apr-23 103526.5 11 0 0.15 23-Apr-23 87997.525 12 0 0.15 24-Apr-23 74797.89625 13 this a new data with much more variablity in the rate.
It would be great relief if you can help me with this.
- tamerj1
sorry I did not understand your problem. Please explain further and don't assume I have any idea about your data, calculations or requirements.
- NaveenMD
If you see the date 19 April 23 the Material Quantity (96800) should be multiplied by 0.8 ie. (1-0.2) and the result should be 77440 but when we use the above mentioned DAX the Material quantity gets multiplied by 0.85 ie. (1-1.5) and the result optained is 82280.
Somehow the the material quantity of 19th April gets multiplied by rate of 20th April.
DAX:
Closing Stock 2 = VAR MinIndex = MINX ( FILTER ( 'Table', 'Table'[Material QTY] > 0 ), 'Table'[Index] ) VAR T1 = FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) RETURN SUMX ( T1, VAR Qty = 'Table'[Material QTY] VAR T2 = FILTER ( T1, 'Table'[Index] > EARLIER ( 'Table'[Index] ) ) VAR Rate = COALESCE( PRODUCTX ( T2, 1 - 'Table'[Rate] ), 1 ) RETURN Qty * Rate )Do let me know if any other clarificaion is required
Thanks tamerj1