Finding max date in group

[Anonymous]

Hi,

I have created a measure Failed Groups LastWeek, which counts how many groups with Group_Status "Failed" where in last week. I need to adjust measure Failed Groups LastWeek, that it would count Groups only with latest date in that week (groups' names are different). 

E.g. I outlined 4 rows with week number 27 (it's last week), and 3 of them are with status Failed. So now all of them are counted. But I need to count Groups only with the lastest GroupStartTime and then count those rows that are with Group_status "Failed".

Failed Groups LastWeek = CALCULATE(COUNT(SummaryAboutGroups_G[Group]), FILTER(SummaryAboutGroups_G, SummaryAboutGroups_G[Week number]=SummaryAboutGroups_G[Last Week]), FILTER(SummaryAboutGroups_G, SummaryAboutGroups_G[Group_status]="Failed") )

Thanks,

Agne

[sturlaws]

Hi @Anonymous 

Do you still have issues with this?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Cheers,
Sturla

[AntrikshSharma]

Try something like this:

Failed Groups LastWeek =
CALCULATE (
    COUNTROWS ( VALUES ( SummaryAboutGroups_G[Group] ) ),
    FILTER (
        SUMMARIZE (
            SummaryAboutGroups_G,
            SummaryAboutGroups_G[Week number],
            SummaryAboutGroups_G[Last Week],
            SummaryAboutGroups_G[Group_status],
            SummaryAboutGroups_G[Group],
            SummaryAboutGroups_G[GroupStartTime]
        ),
        SummaryAboutGroups_G[Group_status] = "Failed"
            && SummaryAboutGroups_G[GroupStartTime]
                = MAX ( SummaryAboutGroups_G[GroupStartTime] )
    )
)

[Anonymous]

@AntrikshSharma ,

Thank you for your suggestion and sorry for late reply.

Actually, it is not the right measure for this situation. As I didn't find solution yet, I'd like to describe what I want to reach again.

Maybe one measure is not enough, it could be counted separately, but here are the steps that should be done:

1. Measure should filter rows of one week (this week/last week/custom date range)

2. Measure should filter rows with latest date (GroupStartTime) per Group (show row with max date in group)

3. Measure should filter rows with Group_Status "Failed"

4. Measure should count all those rows