Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago
Solved

Earlier Task Duration Greater Than or Less Than

Looking for a DAX formula that would know if the previous day duration was less than the current day duration (meaning the duration increased). If it was true then take the previous day duration, if not take current day duration. 

 

Something along the lines of:

Calculated Remaining Duration= IF Earlier Task ID Remaining Duration is < Remaining Duration, Earlier Remaining Duration, Remaining Duration. 

 

Example below.

 

Date

Task IDRemaining DurationCalculated Remaining Duration

4/26/2022

11616
4/26/202221616
4/27/202212416
4/27/202221616
4/28/2022188
4/28/2022288
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Daxtothemax ,

     

    Please refer this formula.

    Column = 
var pre = CALCULATE(MIN('Table'[Remaining Duration]),FILTER(ALLEXCEPT('Table','Table'[Task ID]),'Table'[Date]=EARLIER('Table'[Date])-1))
return
IF(ISBLANK(pre)||'Table'[Remaining Duration]<pre,'Table'[Remaining Duration],pre)

     

    Best Regards,

    Jay

3 Replies

  • jsaunders_zero9's avatar
    jsaunders_zero9
    Responsive Resident
    4 years ago

    Hi Daxtothemax 

    What about

    RemDuration_Calc = 
    var RemDuration = MAX(Tasks[Remaining Duration])
    var RemDuration_Prev = CALCULATE([RemDuration],DATEADD(_Calendar[Date],-1,DAY))

    RETURN IF(RemDuration_Prev < RemDuration, RemDuration_Prev, RemDuration)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Daxtothemax ,

     

    Please refer this formula.

    Column = 
var pre = CALCULATE(MIN('Table'[Remaining Duration]),FILTER(ALLEXCEPT('Table','Table'[Task ID]),'Table'[Date]=EARLIER('Table'[Date])-1))
return
IF(ISBLANK(pre)||'Table'[Remaining Duration]<pre,'Table'[Remaining Duration],pre)

     

    Best Regards,

    Jay

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Try this calculated column formula

    =if(LOOKUPVALUE(Data[Remaining Duration],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])&&Data[Task ID]=EARLIER(Data[Task ID]))),Data[Task ID],Data[Task ID])<Data[Remaining Duration],LOOKUPVALUE(Data[Remaining Duration],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])&&Data[Task ID]=EARLIER(Data[Task ID]))),Data[Task ID],Data[Task ID]),Data[Remaining Duration])

    Hope this helps.