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 

     

     

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago

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