Forum Discussion
Data manipulation
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
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.
Nathaniel
Measure 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
Hi Nathaniel_C
Yes the calendar date is always sequential and in the calendar order.
Thank you again
Glen
Hi Nathaniel_C ,
I just do a cast whenever i pull the date into powerbi.
cast (cast (ProcessDate as char(8)) as date) as ProcessDate.
Please let me know if that works. But basically, the format would be mm/dd/YYYY
Thank you again
Glen