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