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!

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