Calculating Task Progression

[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!

[TheoC]

@Daxtothemax 

If you wanted the below to work within your existing column (removing the need for 2 columns), just adjust your calculated column to the following:

Diff = 

VAR __t = FILTER ( 'Table' , 'Table'[Task] = EARLIER ( 'Table'[Task] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
VAR __t1 =
   IF ( 
     ISEMPTY ( __t ) , 0 , 
       MAXX ( TOPN ( 1 , __t , 'Table'[Date] , ) , 
       'Table'[Remaining Duration (Hours)] ) - 'Table'[Remaining Duration (Hours)] )
VAR __t2 = 
   SWITCH ( 
      TRUE () ,
        __t1 > 0 , __t1 ,
        __t1 < 0 , 0 ,
           'Table'[Remaining Duration (Hours)] = 0 , MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Task] = 'Table'[Task] && 'Table'[Date] <= EARLIER ('Table'[date] ) - 1 ) , __t1 ) , 'Table'[Change] )

RETURN

__t2

I've added the PBIX for the above to this post.

Hope this helps 

[Ashish_Mathur]

Hi,

These 2 calculated column formulas work

Calculated Column 1 = if(LOOKUPVALUE(Data[Remaining Duration (Hours)],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Task]=EARLIER(Data[Task])&&Data[Date]<EARLIER(Data[Date]))),Data[Task],Data[Task])<Data[Remaining Duration (Hours)],LOOKUPVALUE(Data[Remaining Duration (Hours)],Data[Date],CALCULATE(min(Data[Date]),FILTER(Data,Data[Task]=EARLIER(Data[Task])&&Data[Date]<EARLIER(Data[Date]))),Data[Task],Data[Task])-Data[Remaining Duration (Hours)],LOOKUPVALUE(Data[Remaining Duration (Hours)],Data[Date],CALCULATE(max(Data[Date]),FILTER(Data,Data[Task]=EARLIER(Data[Task])&&Data[Date]<EARLIER(Data[Date]))),Data[Task],Data[Task])-Data[Remaining Duration (Hours)])

Calculated Column 2 = MAX(0,Data[Calculated Column 1])

Hope this helps.

[TheoC]

@Daxtothemax 

If you wanted the below to work within your existing column (removing the need for 2 columns), just adjust your calculated column to the following:

Diff = 

VAR __t = FILTER ( 'Table' , 'Table'[Task] = EARLIER ( 'Table'[Task] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
VAR __t1 =
   IF ( 
     ISEMPTY ( __t ) , 0 , 
       MAXX ( TOPN ( 1 , __t , 'Table'[Date] , ) , 
       'Table'[Remaining Duration (Hours)] ) - 'Table'[Remaining Duration (Hours)] )
VAR __t2 = 
   SWITCH ( 
      TRUE () ,
        __t1 > 0 , __t1 ,
        __t1 < 0 , 0 ,
           'Table'[Remaining Duration (Hours)] = 0 , MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Task] = 'Table'[Task] && 'Table'[Date] <= EARLIER ('Table'[date] ) - 1 ) , __t1 ) , 'Table'[Change] )

RETURN

__t2

I've added the PBIX for the above to this post.

Hope this helps 

[TheoC]

@Daxtothemax 

If you wanted the below to work within your existing column (removing the need for 2 columns), just adjust your calculated column to the following:

Diff = 

VAR __t = FILTER ( 'Table' , 'Table'[Task] = EARLIER ( 'Table'[Task] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
VAR __t1 =
   IF ( 
     ISEMPTY ( __t ) , 0 , 
       MAXX ( TOPN ( 1 , __t , 'Table'[Date] , ) , 
       'Table'[Remaining Duration (Hours)] ) - 'Table'[Remaining Duration (Hours)] )
VAR __t2 = 
   SWITCH ( 
      TRUE () ,
        __t1 > 0 , __t1 ,
        __t1 < 0 , 0 ,
           'Table'[Remaining Duration (Hours)] = 0 , MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Task] = 'Table'[Task] && 'Table'[Date] <= EARLIER ('Table'[date] ) - 1 ) , __t1 ) , 'Table'[Change] )

RETURN

__t2

I've added the PBIX for the above to this post.

Hope this helps 🙂

[Daxtothemax]

Hi @TheoC ,

Thank you for this, it's getting closer.  Still having an issue, Task A on 4/6 should equal 8 based on the original duration.  Any ideas on this?  Thank you again for the help!

[TheoC · Accepted solution]

Hi @Daxtothemax 

Ahhh my bad!  Have amended by adding another VAR __t3 in the calculated column which basically says to get the value where the Task had the most recent value that was not a 0, etc., and it gives the results you're after:

Diff = 

VAR __t = FILTER ( 'Table' , 'Table'[Task] = EARLIER ( 'Table'[Task] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
VAR __t1 =
    IF ( 
        ISEMPTY ( __t ) , 0 , 
            MAXX ( 
                TOPN ( 1 , __t , 'Table'[Date] , ) , 
                    'Table'[Remaining Duration (Hours)] ) - 'Table'[Remaining Duration (Hours)] 
                )
VAR __t2 = 

SWITCH ( 
    TRUE () ,
        __t1 > 0 , __t1 ,
        __t1 < 0 , 0 ,
        'Table'[Remaining Duration (Hours)] = 0 , MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Task] = 'Table'[Task] && 'Table'[Date] <= EARLIER ('Table'[date] ) - 1 ) , __t1 ) ,
        'Table'[Change] )

VAR __t3 = 

SWITCH (
    TRUE () ,
        'Table'[Remaining Duration (Hours)] = 0 && __t2 > 0 , CALCULATE ( MIN ('Table'[Remaining Duration (Hours)] ) , FILTER ( 'Table' , 'Table'[Task] = EARLIER ('Table'[Task] ) && 'Table'[Remaining Duration (Hours)] > 0 ) ) , __t2 )

RETURN

__t3

The output is per below:

And a copy of the new PBIX is attached 🙂

All the best mate!

Theo 

[TheoC]

Hi   

You can achieve what you want by adding another Calculated Column like the below. A PBIX file is also attached to further assist if required.

Diff 2 = 

VAR _1 = 
   SWITCH ( 
     TRUE () ,
        'Table'[Diff] > 0 , 'Table'[Diff] ,
        'Table'[Diff] < 0 , 0 ,
        'Table'[Remaining Duration (Hours)] = 0 , MAXX ( FILTER ( ALL ( 'Table' ) , 'Table'[Task] = 'Table'[Task] && 'Table'[Date] <= EARLIER ('Table'[date] ) - 1 ) , 'Table'[Diff] ) ,
        'Table'[Change] )

RETURN

_1

Hope this helps!

Theo 🙂