How to compare data in multiple rows in a table

[dark_z]

Hi

I have a dataset similar to the table below, the table contains two activities with one row per step that generate progress/percent complete. I would like to get a column with a "1" or bool true in to indicate what row has the newest ActualDate per activity, and one column with a "1" or bool true to indicate what is the next step within the activity (row with oldest planned date without actual date)

ActivityID	ActivityName	ActualDate	PlannedDate	PercentComplete
A1000	Activity 1	10.07.2017	10.07.2017	25
A1000	Activity 1	16.09.2017	10.09.2017	50
A1000	Activity 1		02.10.2017	60
A1000	Activity 1		28.10.2017	75
A1000	Activity 1		03.11.2017	100
A2000	Activity 2	03.10.2017	02.10.2017	10
A2000	Activity 2	05.10.2017	06.10.2017	20
A2000	Activity 2	12.10.2017	12.10.2017	50
A2000	Activity 2		15.10.2017	60
A2000	Activity 2		20.10.2017	70
A2000	Activity 2		28.10.2017	100

The result should be something like this :

ActivityID	ActivityName	ActualDate	PlannedDate	PercentComplete	LastActual	NextPlanned
A1000	Activity 1	10.07.2017	10.07.2017	25
A1000	Activity 1	16.09.2017	10.09.2017	50	1
A1000	Activity 1		02.10.2017	60		1
A1000	Activity 1		28.10.2017	75
A1000	Activity 1		03.11.2017	100
A2000	Activity 2	03.10.2017	02.10.2017	10
A2000	Activity 2	05.10.2017	06.10.2017	20
A2000	Activity 2	12.10.2017	12.10.2017	50	1
A2000	Activity 2		15.10.2017	60		1
A2000	Activity 2		20.10.2017	70
A2000	Activity 2		28.10.2017	100

Can anyone help me out with regards to where I should start?

[Zubair_Muhammad]

Hi @dark_z

Add this calculated column for Lastactual

LastActual =
IF (
    Table1[ActualDate]
        = MAXX (
            FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) ),
            Table1[ActualDate]
        ),
    1
)

[dark_z]

Thanks a lot. Actually, that solved my initial problem!

However, when i look closer at the data, i found an issue using what I was trying to do as some of the activities has the same actual date on several rows and I need only the row with the highest Percent Complete. I have updated the table below to show how the result became. For "Activity 1" i only want a "1" in LastActual on the row with 50% complete, not the one with 25% complete. Are you able to help me out with that as well?

ActivityID	ActivityName	ActualDate	PlannedDate	PercentComplete	LastActual	NextPlanned
A1000	Activity 1	16.09.2017	10.07.2017	25	1
A1000	Activity 1	16.09.2017	10.09.2017	50	1
A1000	Activity 1		02.10.2017	60		1
A1000	Activity 1		28.10.2017	75
A1000	Activity 1		03.11.2017	100
A2000	Activity 2	03.10.2017	02.10.2017	10
A2000	Activity 2	12.10.2017	06.10.2017	20	1
A2000	Activity 2	12.10.2017	12.10.2017	50	1
A2000	Activity 2		15.10.2017	60		1
A2000	Activity 2		20.10.2017	70
A2000	Activity 2		28.10.2017	100

[Zubair_Muhammad · Accepted solution]

HI @dark_z

This is the revised Formula

LastActual=
VAR MyMax =
    MAXX (
        FILTER (
            Table1,
            Table1[ActivityName] = EARLIER ( Table1[ActivityName] )
                && NOT ( ISBLANK ( Table1[ActualDate] ) )
        ),
        Table1[PercentComplete]
    )
RETURN
    IF (
        Table1[ActualDate]
            = MAXX (
                FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) ),
                Table1[ActualDate]
            )
            && Table1[PercentComplete] = MyMax,
        1
    )

[Zubair_Muhammad]

Hi @dark_z

Following calculated column for Next Planned

NextPlanned =
IF (
    Table1[PlannedDate]
        = MINX (
            FILTER (
                Table1,
                Table1[ActivityName] = EARLIER ( Table1[ActivityName] )
                    && ISBLANK ( Table1[ActualDate] )
            ),
            Table1[PlannedDate]
        ),
    1
)