Grouping and Comparing Dates Across Columns and Rows

[Anonymous]

Hello, I have a table with IDs, States, and two different Date columns that are of interest; any ID can have multiple values for either or both of the Date columns. My goal is, for each ID, to compare the MAX values in "CICD Ready Date" and "FUT Ready Date" - and if the MAX(CICD Ready Date) is greater than the MAX(FUT Ready Date), then in a new Column "Is CICD After FUT", return a "Yes".

In this example, the ID 9061 has a MAX(CICD Ready Date) of 6/7/19; and a MAX(FUT Ready Date) of 4/29/19; so it would evaluate to True and record a "Yes" in the desired new column. 

As opposed to ID 9436 which has a MAX(CICD Ready Date) of 4/29/19 and MAX(FUTReady Date) of 6/4/19, so it would evaluate to False / No. 

I would normally do this by grouping by ID and then comparing the dates in line in SQL, but I'm not sure how to do it directly in PBI.

Thanks!

[TeigeGao]

Hi @Anonymous ,

Assume that we have dataset like below:

We can use the following DAX query to create a measure:

Is CICD After FUT =
IF (
    CALCULATE (
        MAX ( 'Table'[CICD Ready Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[id] = MIN ( 'Table'[id] ) )
    )
        > CALCULATE (
            MAX ( 'Table'[FUT Ready Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[id] = MIN ( 'Table'[id] ) )
        ),
    "Yes",
    "No"
)

The result will like below:

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao  - this is great, thanks! Just one more thing I didn't explain earlier; my goal is to display a count of "Yes" (or use 0=false, 1=true; and get a sum for the "1" values in the "Is CICD After FUT" column). This count / sum should be based on a distinct value per each "Work Item ID" - in other words, only count a "yes" once for each Work Item ID.

With the measure, I don't know how to do this... I went ahead and changed your formula to output "1" / "0" instead of "yes" / "no"... then I created a column with a count based on this measure, but it counts every row, instead of once per Distinct "Work Item ID" which is what I'm looking for.

So, ID 9061 shows a count of 4:

So in this example, I get a total of 12, whereas I would like to see this count be equal to 7 (i.e. count each "1" value in the "Is CICD after FUT" column as displayed above).

For clarity, I understand this is because the underlying data has 4 rows with a "1" for ID 9061