Forum Discussion
Helper IDynamically referencing above rows in the same column
NaveenMD
Here you goClosing 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 )
Helper ISorry 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=103277306777121863015&rtpof=true&sd=true
Community ChampionNaveenMD
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
)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.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!
