Difference between two values in same column

[Daxtothemax]

Looking for a way in Power BI to calculate the difference between values in the same column. Need the day over day change to the remaining duration. I have a formula that works but an issue has come up when durations are actually increased.  I would prefer to not show negative change and the change be based off the original duration only. Please see existing formula and example data below. 

Any help is greatly appreciated!

[Anonymous · Accepted solution]

Hi @Daxtothemax ,

Please check the formula.

_flag = 
var a = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[date]=EARLIER(DS[date])-1&&DS[Task]=EARLIER(DS[Task])))
return
if(a=blank()||a>=DS[Remaining Duration],1,0)

change = 
var _date = CALCULATE(MAX(DS[date]),FILTER(DS,DS[date]<EARLIER(DS[date])&&DS[Task]=EARLIER(DS[Task])&&DS[_flag]=1))
var _value = CALCULATE(SUM(DS[Remaining Duration]),FILTER(DS,DS[Task]=EARLIER(DS[Task])&&DS[date]=_date))
return
IF(_value <DS[Remaining Duration],0,_value-DS[Remaining Duration])

Best Regards,

Jay

[tamerj1]

Hi @Daxtothemax 

You may try

Diff =
VAR TaskTable =
    CALCULATETABLE ( DS, ALLEXCEPT ( DS, DS[Task] ) )
VAR FirstDate =
    MINX ( TaskTable, DS[Date] )
VAR FirstDuration =
    MAXX ( FILTER ( TaskTable, DS[Date] = FirstDate ), DS[Hrs] )
VAR Result = FirstDuration - DS[Hrs]
RETURN
    IF ( Result < 0, 0, Result )