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

    • JoaoSimas's avatar
      JoaoSimas
      New Member
      1 year ago

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

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