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 Itamerj1 this is where I am facing an issue by using the DAX
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 QTY | Rate | Date | Correct Closing stock | Index |
| 0 | 0.2 | 11-Apr-23 | 0 | 0 |
| 0 | 0.2 | 12-Apr-23 | 0 | 1 |
| 0 | 0.2 | 13-Apr-23 | 0 | 2 |
| 0 | 0.2 | 14-Apr-23 | 0 | 3 |
| 0 | 0.2 | 15-Apr-23 | 0 | 4 |
| 0 | 0.2 | 16-Apr-23 | 0 | 5 |
| 150000 | 0.2 | 17-Apr-23 | 150000 | 6 |
| 1000 | 0.2 | 18-Apr-23 | 121000 | 7 |
| 0 | 0.25 | 19-Apr-23 | 90750 | 8 |
| 100000 | 0.15 | 20-Apr-23 | 168062.5 | 9 |
| 0 | 0.12 | 21-Apr-23 | 147895 | 10 |
| 0 | 0.3 | 22-Apr-23 | 103526.5 | 11 |
| 0 | 0.15 | 23-Apr-23 | 87997.525 | 12 |
| 0 | 0.15 | 24-Apr-23 | 74797.89625 | 13 |
this a new data with much more variablity in the rate.
It would be great relief if you can help me with this.
Community Championsorry I did not understand your problem. Please explain further and don't assume I have any idea about your data, calculations or requirements.
- NaveenMD
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