Forum Discussion

TheHans's avatar
TheHans
Icon for Helper I rankHelper I
5 years ago
Solved

Determine progress change for same items with different date

Dear all, I am pretty new to PowerBi and so far I could solve my problems with simple research. Now I come to a point where I could not find any solution for my question.   I have following table ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  TheHans ,

     

    You can also use below dax expressions:

    Progress previous date = 
var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]<EARLIER('Table'[Date])))
Return
IF(_date=BLANK(),0,CALCULATE(MAX('Table'[Progress]),FILTER('Table','Table'[Date]=_date&&'Table'[ID]=EARLIER('Table'[ID]))))
    difference = IF('Table'[Progress previous date]=0,0, 'Table'[Progress]-'Table'[Progress previous date])

     And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

You can do it in either place. Ask yourself if the result can be influenced by user interaction with your reports (setting an additional date filter, for example).  If the answer is no then you can do it in Power Query or in a calculated column, otherwise you need to create a DAX measure. I think the latter is more likely - please confirm.

 

Search for the "previous row"  pattern, there are lots of examples on how to do that.

TheHans's avatar
TheHans
Icon for Helper I rankHelper I
5 years ago

Thanks for the fast reply .

Then I guess it would be rather DAX in order to remain flexible.

I basically would like to show the progress per ID compared to the last date for the same ID.

 

How would I do that?

Regards

Hans

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  TheHans ,

     

    You can also use below dax expressions:

    Progress previous date = 
var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]<EARLIER('Table'[Date])))
Return
IF(_date=BLANK(),0,CALCULATE(MAX('Table'[Progress]),FILTER('Table','Table'[Date]=_date&&'Table'[ID]=EARLIER('Table'[ID]))))
    difference = IF('Table'[Progress previous date]=0,0, 'Table'[Progress]-'Table'[Progress previous date])

     And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • TheHans's avatar
      TheHans
      Icon for Helper I rankHelper I
      5 years ago

      Very nice. You made my day. Thanks a lot. the only thing I had to correct was following:

      difference = IF('Table'[Progress previous date]=0,0, 'Table'[Progress]-'Table'[Progress previous date])​

      the value to compare with in the "if" statement needs to be "'Table'[Progress previous date]" with "'Table'[Progress]", otherwise, when the progress changes from "0" "50", the difference will still be "0".

      So I changed it to:

      difference = IF('Table'[Progress previous date]='Table'[Progress],0, 'Table'[Progress]-'Table'[Progress previous date])