topic Re: Dynamically referencing above rows in the same column in DAX Commands and Tips

Dynamically referencing above rows in the same column

NaveenMD — Thu, 13 Apr 2023 15:52:16 GMT

Hi Community,

How can we reference the row above using dax

Material QTY	Rate	Date	X Closing Stock	Correct Closing stock	Index
0	0.2	11-Apr-23	0	0	0
0	0.2	12-Apr-23	0	0	1
0	0.2	13-Apr-23	0	0	2
0	0.2	14-Apr-23	0	0	3
0	0.2	15-Apr-23	0	0	4
0	0.2	16-Apr-23	0	0	5
150000	0.2	17-Apr-23	150000	150000	6
1000	0.2	18-Apr-23	121000	121000	7
0	0.2	19-Apr-23	96800	96800	8
100000	0.15	20-Apr-23	177440	177440	9
0	0.15	21-Apr-23	146952	150824	10
0	0.15	22-Apr-23	121811.6	128200.4	11
0	0.15	23-Apr-23	101061.78	108970.34	12
0	0.15	24-Apr-23	83920.049	92624.789	13

need to calculate the data in correct closing stock (excel formula used is: =E2*(1-B3)+A3 in the E3)

with the help tamerj1 and making minor changes to the solution given by tamerj1 to a similar problem I could come up with

Closing Stock =

VAR MinDate = MIN ( 'Table'[Date] )

VAR T1 = FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )

var Summ= SUMX (

T1,

var Mrate = 'Table'[Rate]

VAR Qty = LOOKUPVALUE('Table'[Material QTY],'Table'[Index],'Table'[Index]-1)

VAR T2 = FILTER ( T1, 'Table'[Date] >= EARLIER ( 'Table'[Date] ) )

var Rate2 = if('Table'[Index]=0,1,(1 -Mrate ))

VAR Rate =

PRODUCTX (

T2,

IF ( 'Table'[Date] = MinDate, 1, (1 -Mrate ) )

)

RETURN

Rate*Qty

)

return

Summ+'Table'[Material QTY]

this gives us the value in X closing stock.

Re: Dynamically referencing above rows in the same column

Greg_Deckler — Thu, 13 Apr 2023 16:27:38 GMT

NaveenMD 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

Re: Dynamically referencing above rows in the same column

tamerj1 — Fri, 14 Apr 2023 10:16:47 GMT

NaveenMD
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 )

Re: Dynamically referencing above rows in the same column

NaveenMD — Sun, 16 Apr 2023 09:24:05 GMT

tamerj1 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.

Re: Dynamically referencing above rows in the same column

tamerj1 — Sun, 16 Apr 2023 12:44:46 GMT

NaveenMD

sorry I did not understand your problem. Please explain further and don't assume I have any idea about your data, calculations or requirements.

Re: Dynamically referencing above rows in the same column

NaveenMD — Sun, 16 Apr 2023 13:43:56 GMT

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

Re: Dynamically referencing above rows in the same column

tamerj1 — Sun, 16 Apr 2023 18:19:34 GMT

NaveenMD

"E2*(1-B3)+A3 in the E3" this is your excel formula. I plugged it in an excel sheet and got exactly the same results of the DAX formula. Please specify the correct excel formula to work with.

Re: Dynamically referencing above rows in the same column

NaveenMD — Mon, 17 Apr 2023 03:25:53 GMT

Sorry for the concern tamerj1 !

Excel Formula : =D2*(1-B2)+A3

Material QTY	Rate	Date	Correct Closing stock
0	0.2	11-Apr-23	0
0	0.2	12-Apr-23	0
0	0.2	13-Apr-23	0
0	0.2	14-Apr-23	0
0	0.2	15-Apr-23	0
0	0.2	16-Apr-23	0
150000	0.2	17-Apr-23	150000
1000	0.2	18-Apr-23	121000
0	0.2	19-Apr-23	96800
100000	0.15	20-Apr-23	177440
0	0.15	21-Apr-23	150824
0	0.15	22-Apr-23	128200.4
0	0.15	23-Apr-23	108970.34
0	0.15	24-Apr-23	92624.789

You can use the link for accessing the file : https://docs.google.com/spreadsheets/d/1E8obh8vJHHI6OX8FSEzTQ4_iM4OSGdQY/edit?usp=sharing&ouid=103277306777121863015&rtpof=true&sd=true

Re: Dynamically referencing above rows in the same column

tamerj1 — Mon, 17 Apr 2023 10:48:40 GMT

NaveenMD
Here you go

Closing 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 )

Re: Dynamically referencing above rows in the same column

NaveenMD — Wed, 19 Apr 2023 03:34:01 GMT

Thanks tamerj1 this soulution fits perfectly.

Re: Dynamically referencing above rows in the same column

JoaoSimas — Tue, 12 Nov 2024 10:13:21 GMT

Hi tamerj1 - thank you for this very good solution. I managed to solve a very similar problem using the solution from Closing Stock 1, which fits the best for my need. I'm struggling now to make it interactive by filters, meaning that I would like to make it a measure instead of a calculated column.

In my case it's like a have a measure for Material QTY and Rate, then I would like to create te Closing Stock 1 as a measure. Do you think this would be possible?

Thank you!

Re: Dynamically referencing above rows in the same column

tamerj1 — Tue, 12 Nov 2024 13:13:52 GMT

Hi JoaoSimas
Please provide a sample file along with clear illustration of the requirement.

Re: Dynamically referencing above rows in the same column

tamerj1 — Tue, 12 Nov 2024 13:17:12 GMT

Greg_Deckler
This is a nice example of pseudo-recursive calculation that might be helpful to you for your new book.

Re: Dynamically referencing above rows in the same column

JoaoSimas — Wed, 13 Nov 2024 10:28:20 GMT

Hi tamerj1 - find the illustration of the requirement and the sample file below

#Cumulative Supply (Column) = VAR MinIndex = MINX(FILTER(ProjectionYears,ProjectionYears[#Supply Balance (Column)]>0),ProjectionYears[Year Index]) VAR T1 = FILTER(ProjectionYears, ProjectionYears[Year Index] <= EARLIER (ProjectionYears[Year Index])) RETURN SUMX(T1, VAR Qty = ProjectionYears[#Supply Balance (Column)] VAR T2 = FILTER (T1,ProjectionYears[Year Index] >= EARLIER (ProjectionYears[Year Index])) VAR Rate = PRODUCTX (T2,IF(ProjectionYears[Year Index] = MinIndex,1,(1 - [Attrition]))) RETURN Qty * Rate)

#Cumulative Supply (Measure) = VAR CurrentYearIndex = MAX(ProjectionYears[Year]) VAR DecayFactor = 1 - [Attrition] RETURN SUMX(FILTER( ALLSELECTED(ProjectionYears), ProjectionYears[Year] <= CurrentYearIndex), VAR Supply = [#Supply Balance (Measure)] VAR YearDifference = CurrentYearIndex - ProjectionYears[Year] RETURN Supply * POWER(DecayFactor, YearDifference))

Link to .pbix sample file:
https://drive.google.com/file/d/1lqGJ9PFQSAIw6wiZPSd1dAYwuVVSMVNt/view?usp=sharing

Thank you!

Re: Dynamically referencing above rows in the same column

JoaoSimas — Wed, 13 Nov 2024 15:47:55 GMT

Hi tamerj1

I was able to solve the problem by adding a condition for the YearDifference.

Thanks anyway!

#Cumulative Supply (Measure) = VAR CurrentYearIndex = MAX(ProjectionYears[Year Index]) -- Get the current year index VAR DecayFactor = 1 - [Attrition] -- Define the decay factor as 1 minus the attrition rate RETURN SUMX(FILTER( ALLSELECTED(ProjectionYears), ProjectionYears[Year Index] <= CurrentYearIndex), -- Only consider years up to the current year VAR Supply = [#Supply Balance (Measure)] -- Get the supply for the current year VAR YearDifference = CurrentYearIndex - ProjectionYears[Year Index] -- How far back in time this year is VAR FixedDifference = IF(ProjectionYears[Year Index]>=1,YearDifference+1,YearDifference) --CORRECTION RETURN Supply*POWER(DecayFactor,FixedDifference) )