Forum Discussion
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 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.
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))
11 Replies
not clear about this.
What's the expected output based on the sample data you provided?
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:
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 measureMeasure = maxx(FILTER(all('Table'),'Table'[Date]=max('Table'[Date])&&'Table'[status]=max('Table'[status])),'Table'[duedate])
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.
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
I rephrased the question. The DAX formula needs a filter as well
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))
