Forum Discussion

Daxtothemax's avatar
Daxtothemax
Helper I
4 years ago

Previous Task ID Value IF 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.

 

 

Need Help

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Daxtothemax 

    pls try this

    Column = 
VAR _last=maxx(FILTER('Table','Table'[date]<EARLIER('Table'[date])&&'Table'[task id]=EARLIER('Table'[task id])),'Table'[date])
VAR _duration=maxx(FILTER('Table','Table'[date]=_last&&'Table'[task id]=EARLIER('Table'[task id])),'Table'[remining duration])
return if(ISBLANK(_duration)||'Table'[remining duration]<_duration,'Table'[remining duration],_duration)

    • Daxtothemax's avatar
      Daxtothemax
      Helper I
      4 years ago

      Hi ryan_mayu 

      Thank you, this works but the tasks can have multiple increases in Durations.  So I need the formula to reference the original first duration listed not just the previous date duration.  Any ideas? Thanks again!

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        Daxtothemax 

        is this what you want?

        Column = 
var _first=minx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&'Table'[taskid]=EARLIER('Table'[taskid])),'Table'[date])
return maxx(FILTER('Table','Table'[taskid]=EARLIER('Table'[taskid])&&'Table'[date]=_first),'Table'[remaining duration])