Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Grouping and Comparing Dates Across Columns and Rows

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. 

DevTracker Date Comparison Column.png

 

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!

2 REPLIES 2
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

Assume that we have dataset like below:

PBIDesktop_MMu5HXVi1x.png

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:

PBIDesktop_e5Yyaxpj5j.png

Best Regards,

Teige

Anonymous
Not applicable

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:2019-06-26_9-51-44.png

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 90612019-06-26_9-52-55.png

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.