Forum Discussion

NaveenMD's avatar
NaveenMD
Icon for Helper I rankHelper I
3 years ago
Solved

Dynamically referencing above rows in the same column

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
    )
NaveenMD's avatar
NaveenMD
Icon for Helper I rankHelper I
3 years ago

Sorry for the concern tamerj1 !

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

 

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=103277306777121863015&rtpof=true&sd=true

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      JoaoSimas
      New Member
      1 year ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        1 year ago

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