Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

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...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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's avatar
gco
Resolver II
6 years ago

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's avatar
gco
Resolver II
6 years ago

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

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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's avatar
      gco
      Resolver II
      6 years ago

      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's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        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.