iterate between dates in same column

[AP_83]

 

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

[Anonymous · Accepted solution]

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.

[AP_83]

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]

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

[ryan_mayu]

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