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
Progress Previous Date =
VAR d =
MAX ( Progress[Date] )
VAR p =
CALCULATE (
MAX ( Progress[Date] ),
ALLEXCEPT ( Progress, Progress[ID] ),
Progress[Date] < d
)
RETURN
CALCULATE (
MAX ( Progress[Progress] ),
ALLEXCEPT ( Progress, Progress[ID] ),
Progress[Date] = p
)
TheHans's avatar
TheHans
Icon for Helper I rankHelper I
5 years ago

Hi Ilbendlin,

the value in each column is the difference between the most uptodate value compared with the first value.

So lets say for ID A we do have in total three values with three dates, the difference is the value of the last one compared to the first one. Means when in the first date the progress was 0, second date progress 25% and third date progress 75% the right value should be 50% (75-25). However with this formula the difference is 75% (75-0).