Calculate the number of distinct teams where the earliest date is greater than the latest date score

[MithSingh]

Hello,

I need to calculate the number of distinct teams where the earliest date is greater than the latest date score, and I want to apply date filtering using a slicer. please help me out.

Thanks

[MithSingh]

so if we select dates through silicer from 1/1/2023 to 1/2/2023, then 

These Teams will meet the criteria:
Team B because it's earliest date has a score 5 and the latest date score has 2, so the earliest date is greater than the latest date score
Team D because it's earliest date has a score 6 and the latest date score has 2, so the earliest date is greater than the latest date score

so measure should return the result = 2, total unique team count

[Prateek97 · Accepted solution]

Hi @MithSingh ,

Thanks for explaining the issue again.

I tried the following measure. This should work.

Test = 

var min_date = MIN(Score[Date])

var max_date = MAX(Score[Date])

var table1 = SUMMARIZE(Score,Score[Team])

var table2 = ADDCOLUMNS(table1,"MinScore",CALCULATE(SUM(Score[Score]),Score[Date]=min_date),"MaxScore",CALCULATE(SUM(Score[Score]),Score[Date]=max_date))

var table3 = ADDCOLUMNS(table2,"Flag",IF([MinScore]>[MaxScore],1,0))

return

COUNTX(FILTER(table3,[Flag]=1),DISTINCTCOUNT([Team]))

If this works, please mark this reply as the solution.

Thank you!

[MithSingh]

Yes, It's working. Thank you Sir:) It's a great help.😊😊

[Prateek97]

Hi @MithSingh ,

Can you please explain in little detail. Out of the rows that you have shared which row will meet the criteria you have mentioned.

What I could understand is you want the Count of Teams where the Earliest date's score is greater than the Latest Date's score.