Last Date with Filter DAX

[Anonymous]

Hello There,

I am wondering if I should be using power BI to calculate my last date based on certain condition.

For ex:

I have a below table:

Batch Name| Process Name| Status | last successful run

For each failed process for today, I want to show when was it ran successfully last time?

So basically find the last date where status for the process is passed.

I can have a batch and under batch, I will have 25 processes and I need to see the last successful date for the each process if is failed today.

I can do it at source by creating a view, but I don't want to include additional step tp cater this. It would be good if I can do it through DAX.

Any suggestions? 

[Karolina411]

I am working on a similar problem but need to identify the NAME by substring as they contain the date in the Name too but it is not working: (the goal is to display the date)

Meridian Membership = CALCULATE(Max('JHNMeridian'[Date created]),FILTER('JHNMeridian',[Name] in {"Membership"}))

[Greg_Deckler]

You should be able to create a measure like:

Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Status]="Success"))

Put this in a table along with your columns you specified and you should be good to go.

[Anonymous]

Thanks for the quick response @Greg_Deckler & @Eric_Zhang.

Yes, the DAX looks correct but when I use it in my report, it filters the entire table and shows only successful processes. 

So Basically, it doesn't show failed process and last successful date against it.

So in the above test report, I need a last date against the first record, however, rest of the records should be visible too. 

When I use above DAX,  it filters all the record for status = "Completed".

[Anonymous]

Just to give you an overall view,

It should look something like below:

Batch Name | Process Date| Process Name  | status | last date

B1 | 12-05-2017|P1 | PN1| Completed |  (NULL)

B2 | 13-05-2017|P2 | PN2| Completed |  (NULL)

B1 | 14-05-2017|P1 | PN1| Failed| 12-05-2017

[Eric_Zhang]

@Anonymous

Try

Measure = 
VAR LastSuccessDate =CALCULATE (
    MAX ( 'Table'[Process Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Batch Name], 'Table'[Process Name] ),
    FILTER ( 'Table', [Status] = "Completed" )
)
RETURN IF(ISBLANK(LastSuccessDate),"",LastSuccessDate)

[Anonymous]

@Eric_Zhang Thanks again, but this isn't working.

It is reproducing my records in the table for multiple times.  Repeated records are being displayed. 

[Eric_Zhang]

---

@Anonymous wrote:

@Eric_Zhang Thanks again, but this isn't working.

It is reproducing my records in the table for multiple times.  Repeated records are being displayed. 

---

@Anonymous

Could you share a pbix with sample file? You can upload it to any web storage and share the download link.

[Anonymous]

@Eric_Zhang Apologies, I will not be able to share it as per my org policy. 😞

I can explain the scenario here, 

I have batch table which is my batch dim

I have process dim

Process Audit and Batch audit are my facts and I am trying to generate the report using these tables.

The report will look like:

Batch name | Batch start | Batch End | Batch status | process Name| process start | process end| process status| last successful run

One batch will have multiple processes running on different days and if the process fails, I need to show when was the last time this process run successfully.

I tried creating calculated column using below DAX as provided by you.

Last_date_col =
CALCULATE (
MAX ('process_audit_dup'[process_start]),
FILTER ('process_audit_dup', 'process_audit'[process_status] = "Completed" && RELATED([process_guid])= 'process_audit_dup'[process_guid])
)

What I did was, created a duplicate table with the same column and filter the duplicate table and then joining with original table to find the last date. But Still, this isn't working. 

[Eric_Zhang]

---

@smoupre wrote:

You should be able to create a measure like:

Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table',[Status]="Success"))

Put this in a table along with your columns you specified and you should be good to go.

---

@Greg_Deckler

Just in case, you may use the process date('Table'[Date]) in a slicer,

Measure =
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Batch Name], 'Table'[Process Name] ),
    FILTER ( 'Table', [Status] = "Success" )
)