Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper 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 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'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

2 Replies

  • tamerj1's avatar
    tamerj1
    Community 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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    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