Dynamically referencing above rows in the same column

[NaveenMD]

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.

[NaveenMD]

Thanks @tamerj1  this soulution fits perfectly.

[NaveenMD]

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=10327...

[tamerj1 · Accepted solution]

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

[JoaoSimas]

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!

[tamerj1]

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

[JoaoSimas]

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!

[JoaoSimas]

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

[Greg_Deckler]

@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/3395....
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

[tamerj1]

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

[tamerj1]

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

[NaveenMD]

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

[tamerj1]

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

[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 

[tamerj1]

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