DAX Formula

[ldwf]

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 a calculated column that will show the last Due Date for every Date and Status combination (i.e, columns 1 and 4) and need to filter on two things - 1. where the status is equal to 'Open' or 'Pending'; and 2. where the volume is > 0.  So for the small sample result set below, the Date and Status combination is 2023-03-31 || 'Open', and the last Due Date where the volumne is > 0  is 2023-10-31.  I need to be able to create a variable that would store the value of 2023-10-31 for this combination.  I believe I can create a [virtual] table that does this but believe a DAX formula can be written to derive this.  I tried it but it returned the maximum date for all records in the result set, regardless of the Date and Status combination.  The result set looks like the following:

The desired output of the formula is the value 10/31/2023.

[Ashish_Mathur]

Hi,

Write this calculated column formula

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

Hope this helps.

[ldwf]

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

[Ashish_Mathur · Accepted solution]

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]

That worked!...thanks

[ldwf]

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]

not clear about this. 

What's the expected output based on the sample data you provided? 

[ldwf]

@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]

@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]

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

[ldwf]

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]

could you please provide more sample data and expected output?