Data manipulation

[gco]

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

[Nathaniel_C]

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] ) )

[gco]

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

[Nathaniel_C]

Hi @gco are the dates sequential? Can we always assume that they are in calendar order?Nathaniel

[gco]

Hi @Nathaniel_C 

Yes the calendar date is always sequential and in the calendar order.

Thank you again

Glen

[gco]

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

[gco]

Sorry, the format is YYYY-MM-DD, and then i just change it to MM/dd/YYYY in PowerBI.

[Nathaniel_C · Accepted solution]

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

[gco]

Hi @Nathaniel_C 

Your measure worked perfectly! I am not sure how you guys come up with all these answers.  I am very interested to know how you guys learned all these tricks.

Thank you again.

Glen

[Nathaniel_C]

Thank you Glen for the kind words, you are welcome! 
What books have you read?
Nathaniel.
I can recommend two depending on what level you are at right now. @MattAllington Supercharge Power BI and @KenPuls M is for (Data) Monkey.  Look for patterns and use variables a lot.

[gco]

Hi @Nathaniel_C ,

I have not formally read any books.  But i went to Microsoft free powerbi trainings.

Most of the work i do is on the SQL side, so i tend to design my query so that i don't pull a lot of data into the report.  But most of my calculations are done within powerbi so that it does not slow down my query.  Most of the measures i currently use on all my reports are just repeats of past reports - with some additional features added.  And most of what i need i am able to figure out - just simple dax (based on what i know in SQL, i just try to translate it into dax).  And most of it come from the forums and community.  But i would look at the reference you mentioned and also try to use variables on my future projects.

Thanks again

Glen