Forum Discussion

AP_83's avatar
AP_83
Regular Visitor
4 years ago
Solved

iterate between dates in same column

 

This is sample records from a table, which has data for 2 tasks coulmn name is number for that.

 

1. For each task i have to find the most recent startTime and get the Group for that. IE. for task TSK07925127 most recent  group is LCM. Now now i take this group and search for the same task rows, which i match at 3rd line at startTime 11-04-2022 17:04.

so now i have date range for startTime as MIN as 11-04-2022 17:04 and MAX as 21-04-2022 14:59.

now i need to iterate between those time periods and check if myVendor column is TRUE() and AG_Type=Primary Assignment then i will mark that entire task as "Auto" else "Non-Auto".

 

Please let me know in case of any more details

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi AP_83 ,

     

    Please create following measures:

    Last =
VAR _maxDate =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [StartTime]
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Group] ),
        FILTER (
            ALL ( 'Table' ),
            [Number] = MAX ( 'Table'[Number] )
                && [StartTime] = _maxDate
        )
    )

    Last_Group = IF(MAX('Table'[Group]) =[Last],[Last])
    Last_StartTime = IF(MAX('Table'[Group]) =[Last],MAX('Table'[StartTime]))
    Entire Task =
VAR _min =
    MINX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [Last_StartTime]
    )
VAR _max =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [Last_StartTime]
    )
RETURN
    IF (
        MAX ( 'Table'[StartTime] ) >= _min
            && MAX ( 'Table'[StartTime] ) <= _max,
        IF (
            MAX ( 'Table'[AG_Type] ) = "Primary Assignment"
                && SELECTEDVALUE ( 'Table'[myVendor] ) = TRUE (),
            "Auto",
            "Non-Auto"
        ),
        BLANK ()
    )

    Output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    AP_83 

    not clear about this.

    1. get the last record for each task and get group name

    2. find the same group name for the same task

    3. we can get the min date and max date for each task, then why do you need these dates?

    4. what are the next steps?

    pls paste the sample data , not the screenshot. 

  • AP_83's avatar
    AP_83
    Regular Visitor
    4 years ago

    Please find the below

    1. get the last record for each task and get group name  Ans:    last record - Max startTime for task and corosponding group which i already got in last 2 columns
    1. find the same group name for the same task  Ans:    find the group name for each task, based on most recent StartTime column, which is already got in last column
    1. we can get the min date and max date for each task, then why do you need these dates?  Ans:  i need to get maxdate for each task and get Group like LCM for TSK07925127 task. i will take this group and find in same task if i am getting this group in earlier rows. if yes i will mark that statTime as min date
    1. what are the next steps?  Ans: so from step 3 i got now MIN and MAX StartTime based on logic explained.

    Now if myVendor column is TRUE() and AG_Type=Primary Assignment then i will mark that entire task as "Auto" else "Non-Auto"

     attaching the sample data

    Number

    AG_Type

    StartTime

    Group

    myVendor

    Last_StartTime

    Last_Group

    TSK07925127

    Primary Assignment

    11-04-2022 11:24

    SERVICEDESK_AS

    FALSE

     

     

    TSK07925127

    Primary Assignment

    11-04-2022 16:10

    IT FIELD SERVICES

    FALSE

     

     

    TSK07925127

    Primary Assignment

    11-04-2022 17:04

    LCM

    TRUE

    11-04-2022 17:04

    LCM

    TSK07925127

    Collaboration

    13-04-2022 21:03

    SERVICEDESK

    TRUE

     

     

    TSK07925127

    Primary Assignment

    13-04-2022 21:07

    SERVICEDESK

    TRUE

     

     

    TSK07925127

    Primary Assignment

    13-04-2022 21:10

    IT SUPPORT

    FALSE

     

     

    TSK07925127

    Primary Assignment

    14-04-2022 14:07

    IT FIELD SERVICES

    FALSE

     

     

    TSK07925127

    Primary Assignment

    18-04-2022 14:04

    IT SUPPORT

    FALSE

     

     

    TSK07925127

    Collaboration

    13-04-2022 21:10

    SERVICEDESK_AS

    FALSE

     

     

    TSK07925127

    Primary Assignment

    19-04-2022 17:55

    IT FIELD SERVICES

    FALSE

     

     

    TSK07925127

    Primary Assignment

    21-04-2022 14:59

    LCM

    TRUE

    21-04-2022 14:59

    LCM

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      4 years ago

      AP_83 

      what's the problem you are facing? you are stuck at which step?

       

      Now if myVendor column is TRUE() and AG_Type=Primary Assignment then i will mark that entire task as "Auto" else "Non-Auto"

      if you want to achieve this, you don't need to use the results you got from the first 3 steps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AP_83 ,

     

    Please create following measures:

    Last =
VAR _maxDate =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [StartTime]
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Group] ),
        FILTER (
            ALL ( 'Table' ),
            [Number] = MAX ( 'Table'[Number] )
                && [StartTime] = _maxDate
        )
    )

    Last_Group = IF(MAX('Table'[Group]) =[Last],[Last])
    Last_StartTime = IF(MAX('Table'[Group]) =[Last],MAX('Table'[StartTime]))
    Entire Task =
VAR _min =
    MINX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [Last_StartTime]
    )
VAR _max =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Number] = MAX ( 'Table'[Number] ) ),
        [Last_StartTime]
    )
RETURN
    IF (
        MAX ( 'Table'[StartTime] ) >= _min
            && MAX ( 'Table'[StartTime] ) <= _max,
        IF (
            MAX ( 'Table'[AG_Type] ) = "Primary Assignment"
                && SELECTEDVALUE ( 'Table'[myVendor] ) = TRUE (),
            "Auto",
            "Non-Auto"
        ),
        BLANK ()
    )

    Output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.