Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Last Date with Filter DAX

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? 

 

Need Help

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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.

     

     

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago
      @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" )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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

  • Karolina411's avatar
    Karolina411
    Helper V
    2 years ago

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