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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NaveenMD
Helper I
Helper I

Dynamically referencing above rows in the same column

Hi Community,

How can we reference the row above using dax 

Material QTYRateDateX Closing StockCorrect Closing stock Index
00.211-Apr-23000
00.212-Apr-23001
00.213-Apr-23002
00.214-Apr-23003
00.215-Apr-23004
00.216-Apr-23005
1500000.217-Apr-231500001500006
10000.218-Apr-231210001210007
00.219-Apr-2396800968008
1000000.1520-Apr-231774401774409
00.1521-Apr-2314695215082410
00.1522-Apr-23121811.6128200.411
00.1523-Apr-23101061.78108970.3412
00.1524-Apr-2383920.04992624.78913

 

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.
 
1 ACCEPTED SOLUTION

@NaveenMD 
Here you go

1.png

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
    )

View solution in original post

9 REPLIES 9
NaveenMD
Helper I
Helper I

Thanks @tamerj1  this soulution fits perfectly.

NaveenMD
Helper I
Helper I

Sorry for the concern @tamerj1 !

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

 

NaveenMD_0-1681701861428.png

Material QTYRateDateCorrect Closing stock 
00.211-Apr-230
00.212-Apr-230
00.213-Apr-230
00.214-Apr-230
00.215-Apr-230
00.216-Apr-230
1500000.217-Apr-23150000
10000.218-Apr-23121000
00.219-Apr-2396800
1000000.1520-Apr-23177440
00.1521-Apr-23150824
00.1522-Apr-23128200.4
00.1523-Apr-23108970.34
00.1524-Apr-2392624.789

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

@NaveenMD 
Here you go

1.png

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
    )
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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 

1.png

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.

2.png

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
    )

@tamerj1  this is where I am facing an issue by using the DAX 

NaveenMD_0-1681636591484.png

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 QTYRateDateCorrect Closing stock Index
00.211-Apr-2300
00.212-Apr-2301
00.213-Apr-2302
00.214-Apr-2303
00.215-Apr-2304
00.216-Apr-2305
1500000.217-Apr-231500006
10000.218-Apr-231210007
00.2519-Apr-23907508
1000000.1520-Apr-23168062.59
00.1221-Apr-2314789510
00.322-Apr-23103526.511
00.1523-Apr-2387997.52512
00.1524-Apr-2374797.8962513

 

this a new data with much more variablity in the rate.

 

It would be great relief if you can help me with this.

 

 

 

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

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 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors