Forum Discussion
Data manipulation
Hi there,
I am trying to make a measure in PowerBI from the following data.
| Code | ProcessDate | TransDate | Value | NewValue |
| 1 | 20190630 | 20190629 | 10 | 25 |
| 1 | 20190701 | 20190701 | 25 | 25 |
| 1 | 20190702 | 20190702 | 30 | 30 |
I wanted the NewValue to become the next day's value when Processdate <> TransDate. So in the above example, the new value for ProcessDate '20190630' is 25 based on the next day value.
Thank you
Glen
Hi gco ,
Measure NewValue = var _pDate = MAX(NVAL[ProcessDate]) var _tDate = MAX(NVAL[TransDate]) var _tValue = Calculate(MAX(NVAL[Value]), ALLEXCEPT(NVAL,NVAL[ProcessDate],NVAL[Code]),NVAL[ProcessDate]=_pDate+1) return IF (_pDate <> _tDate,_tValue, MAX(NVAL[Value]))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
10 Replies
Hi gco ,
I added an Index col in Power Query.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielMeasure NewValue = VAR _pDate = MAX ( NVAL[ProcessDate] ) VAR _tDate = MAX ( NVAL[TransDate] ) VAR _curIndex = MAX ( NVAL[Index] ) VAR _tValue = CALCULATE ( MAX ( NVAL[Value] ), ALLEXCEPT ( NVAL, NVAL[Index] ), NVAL[Index] = _curIndex + 1 ) RETURN IF ( _pDate <> _tDate, _tValue, MAX ( NVAL[Value] ) )Hi Nathaniel_C ,
Thanks for the very quick response. Your measure would have worked - I like how you came up with it. However, i left out some details on the table. I am pulling different codes at the same time, so it won't work if i use the index. Is it possible to have the measure look for the next identical code? Thank you again.
Code ProcessDate TransDate Value NewValue 1 20190630 20190629 10 25 2 20190630 20190630 10 10 3 20190630 20190630 50 50 1 20190701 20190701 25 25 4 20190701 20190701 60 60 5 20190701 20190701 65 65 Hi gco are the dates sequential? Can we always assume that they are in calendar order?Nathaniel
