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!

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

Calculating Task Progression | Microsoft Fabric Community

7 Replies

TheoC

Community Champion

4 years ago

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 🙂

DaxToTheMax.pbix40 KB

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 🙂

DaxToTheMax2.pbix39 KB

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

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

DaxToTheMax2.pbix40 KB

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

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

DaxToTheMax2.pbix40 KB

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

DaxToTheMax2.pbix40 KB

Forum Discussion

Calculating Task Progression

7 Replies

Related content

visualize task

Measure calculation of cumulative progress %

Progressive Date Columns

Calculate Task Backlog

DAX Progress calculation