topic Last Date with Filter DAX in Developer

Last Date with Filter DAX

Anonymous — Thu, 11 May 2017 07:03:02 GMT

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?

Re: Last Date with Filter DAX

Greg_Deckler — Thu, 11 May 2017 12:33:39 GMT

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.

Re: Last Date with Filter DAX

Eric_Zhang — Sat, 13 May 2017 08:26:30 GMT

@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" )
)

Re: Last Date with Filter DAX

Anonymous — Mon, 15 May 2017 00:23:45 GMT

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".

Re: Last Date with Filter DAX

Anonymous — Mon, 15 May 2017 01:50:07 GMT

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

Re: Last Date with Filter DAX

Eric_Zhang — Mon, 15 May 2017 03:05:40 GMT

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

Re: Last Date with Filter DAX

Anonymous — Mon, 15 May 2017 06:20:21 GMT

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

Re: Last Date with Filter DAX

Eric_Zhang — Mon, 15 May 2017 06:31:51 GMT

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

Re: Last Date with Filter DAX

Anonymous — Mon, 15 May 2017 06:39:48 GMT

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

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.

Re: Last Date with Filter DAX

Karolina411 — Wed, 08 Nov 2023 15:14:57 GMT

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"}))