Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago
Solved

Calculating Task Progression

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

    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 

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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's avatar
TheoC
Community Champion
4 years ago

@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