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 ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi hadi2002 
     
     
    Time Difference1 =
    VAR _a =
    CALCULATE( MAX ( 'Table'[Date/Time] ), ALLEXCEPT('Table','Table'[robot]))
    RETURN
    DATEDIFF (
     
    _a,TODAY(),
    DAY
    )
     
    Max_Date/Time = CALCULATE(MAX('Table'[Date/Time]), ALLEXCEPT('Table','Table'[robot]))
     
     
    Pull Robot, Max_Date/Time and Time Difference1 in a table.
     
    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 ,

     

    You can also use

     

    Time Difference1 =
    VAR _a =
    CALCULATE( MAX ( 'Table'[Date/Time] ), ALLEXCEPT('Table','Table'[robot]))
    RETURN
    DATEDIFF (
     
    _a,NOW(),
    MINUTE
    )
  • hadi2002's avatar
    hadi2002
    Frequent Visitor
    6 years ago

    Hi Anonymous 

    But the result is showing all the data. Is it possible for that particlar measure to return only - list of robots which are having date diff which is less than 30 mins only? Please 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi hadi2002 ,

     

     

    Measure 2 =

    Var _dd = DATEDIFF(MAX('Table'[Date/Time]),NOW(),MINUTE)
    REturn
    //_dd
    CALCULATE(COUNT('Table'[Robot]), FILTER('Table', _dd < 30))
     
     
     
     
     

    Regards,
    Harsh Nathani

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