Forum Discussion

hadi2002's avatar
hadi2002
Frequent Visitor
6 years ago
Solved

Get latest date based on filtered column

Hi All, I have a dt which contain a 'Date' & 'Status' column. I just want to get the most latest date amoung those records which has 'Status'='Success' & 'Exception'. Once get the latest date, I need...
  • Anonymous's avatar
    Anonymous
    6 years ago

    hadi2002 ,

     

    Sorry, can you let me know what is the expected output.

     

    You can put a filter on "Success" .

     

    Also, just change "Postponed" to "Exception"

     

    Queue =

    CALCULATE
    (
    MAX(Table6[TimeString]), FILTER(ALL('Table6'),Table6[Status] ="Success" || Table6[Status] = "EXception" )
    )

     

     

     

     

     

     

    Date DIff = var a = DATEDIFF(MAXX(ALL(Table6),Table6[TimeString]),[Queue],MINUTE)

    RETURN
    a
     
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

hadi2002 ,

 

Syntax Error:

 

Queue = CALCULATE
(
MAX('main'[Date]), FILTER(ALL('main'), main[Status] ="Success" || main[Status] = "Exception" ))
)
 
 
Missed the bracket after MAX.
 
Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

hadi2002 ,

 

 

Suppose you have the below Table6

 

 

Use the measures

 

Queue = CALCULATE
(
MAX(Table6[TimeString]), FILTER(ALL('Table6'),Table6[Status] ="Success" || Table6[Status] = "Postponed" )
)
 
Date DIff = DATEDIFF(MAX(Table6[TimeString]),[Queue],MINUTE)
 
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • hadi2002's avatar
    hadi2002
    Frequent Visitor
    6 years ago

    Hi Anonymous 

    Thanks for you help. But I just wants the most lowest one datediff which has 'Success' & 'Exception' status (ignore postponed) only shown in the card. 😞

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hadi2002 ,

       

      Sorry, can you let me know what is the expected output.

       

      You can put a filter on "Success" .

       

      Also, just change "Postponed" to "Exception"

       

      Queue =

      CALCULATE
      (
      MAX(Table6[TimeString]), FILTER(ALL('Table6'),Table6[Status] ="Success" || Table6[Status] = "EXception" )
      )

       

       

       

       

       

       

      Date DIff = var a = DATEDIFF(MAXX(ALL(Table6),Table6[TimeString]),[Queue],MINUTE)

      RETURN
      a
       
       
       
       

      Regards,
      Harsh Nathani

      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

      • hadi2002's avatar
        hadi2002
        Frequent Visitor
        6 years ago

        hi Anonymous 

        Thanks alot for your help, that really work perfect as what was expected. However, I'm stuck at another last part. Hope you can help me on this to group the data. For example, let's say I have a table as below which shows a column called as Robot and ActionDate (as shown in first picture). 

        I just wants to group the robot column and populate the max date for each respective robots and shows me what is the diffrence from the time now. 

        Your help would be highly appreciated