Forum Discussion

ldwf's avatar
ldwf
Helper V
2 years ago
Solved

DAX Formula

Need help with a DAX formula.  I have a result set that has the five columns as shown in the image.  The number of records is much larger, and there are multiple dates and statuses.  I need to create...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try this

    Latest due date = calculate(max(Data[Due date]),filter(Data,Data[Date]=earlier(Data[Date])&&Data[Status]=earlier(Data[status])&&Data[Volume]>0))

ldwf's avatar
ldwf
Helper V
2 years ago

ryan_mayu...the intent is to create a field that I can use on a visual to show the latest Due Date for every Date & Status combination.  So with the small subset in the screenshot, I want to build a visual that shows three columns, the Date, the Status, and the [latest] Due Date, but only one row.  So the visual should show one row like this:

ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

ldwf @

you can try this to create a column

last due date = CALCULATE(max('Table'[duedate]),ALLEXCEPT('Table','Table'[Date],'Table'[status]))
 
or you can create a measure
Measure = maxx(FILTER(all('Table'),'Table'[Date]=max('Table'[Date])&&'Table'[status]=max('Table'[status])),'Table'[duedate])
  • ldwf's avatar
    ldwf
    Helper V
    2 years ago

    Thanks, but this solution gives me the latest date of all the records in the entire result set, not for each combination of Date and Status that appears in the visual

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      2 years ago

      could you please provide more sample data and expected output?

  • ldwf's avatar
    ldwf
    Helper V
    2 years ago

    I rephrased the question.  The DAX formula needs a filter as well