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 ...
Need Help
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])