Forum Discussion

Daxtothemax's avatar
Daxtothemax
Icon for Helper I rankHelper I
4 years ago
Solved

Difference between two values in same column

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 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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 )