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 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
Sorry, the format is YYYY-MM-DD, and then i just change it to MM/dd/YYYY in PowerBI.