Calculate Group Sum for most recent date available

[JoRo50]

Hi, I'm close on this one, but not quite there.  I hope someone can tell me what I'm missing.

I have a several groups of cohorts that have started a program at different times and I want to be able to compare progress for these cohorts based on time in the program, not current date.  I also want to be able to keep track of a running total of year to date.

I can do the first part, but I'm stuck on the second.

The data is situated as such:

Cohort	Member	Report Date	Start Date	Days From Start	Points
A	Adam	9/1/2022	9/1/2022	0	2
A	Abbie	9/1/2022	9/1/2022	0	1
A	Adam	9/15/2022	9/1/2022	14	6
A	Abbie	9/15/2022	9/1/2022	14	8
A	Adam	9/29/2022	9/1/2022	28	7
A	Abbie	9/29/2022	9/1/2022	28	9
B	Bobby	10/1/2022	10/1/2022	0	1
B	Ben	10/1/2022	10/1/2022	0	1
B	Bobby	10/15/2022	10/1/2022	14	3
B	Ben	10/15/2022	10/1/2022	14	8
B	Bobby	10/29/2022	10/1/2022	28	6
B	Ben	10/29/2022	10/1/2022	28	15
C	Cathy	11/1/2022	11/1/2022	0	2
C	Cindy	11/1/2022	11/1/2022	0	2
C	Cathy	11/15/2022	11/1/2022	14	10
C	Cindy	11/15/2022	11/1/2022	14	11

This allows me to do the comparison by time in the program which looks like this in excel:

When I try to calculate the total points, the total excludes Cohort C, I'm assuming because the number of days from start is lower than the other groups.  The vales for the individual cohorts are correct, but the total should be 58:

I tried 

MaxDate = var max_date = CALCULATE(MAX('Table'[Days From Start]),ALLEXCEPT('Table','Table'[Cohort]))

return

CALCULATE(SUM('Table'[Points]),FILTER('Table','Table'[Days From Start]=max_date))

and I tried

TestMeasure = VAR max_date =CALCULATE (MAX ( 'Table'[Days From Start] ), ALLEXCEPT ( 'Table', 'Table'[Cohort] ) )

RETURN

    CALCULATE (Sum ( 'Table'[Points] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Cohort]) , 'Table'[Days From Start] = max_date) )

Thank you 

[danextian]

Hi @JoRo50 ,

Please try this:

Points (latest date) = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            Data,
            Data[Cohort],
            Data[Report Date],
            Data[Points],
            "Max Date", [Max Report Date by Cohort]
        ),
        "x", IF ( [Max Date] = Data[Report Date], Data[Points] )
    ),
    [x]
)

[JoRo50]

Danextian, I had trouble with this part of the formula: 

[Max Report Date by Cohort]

I tried to do Calculate(Max(Data[ReportDate],Filter(Data,Data[Cohort])),  But I just got an error that Cohort was a string field so it could not calculate.

[amitchandak · Accepted solution]

@JoRo50 , Try two measures like

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Cohort] = max(Data1[Cohort]) ),Data1[Report Start Date])
return
CALCULATE(sum(Data1[Points]), filter( (Data1), Data1[Cohort] = max(Data1[Cohort]) && Data1[Report Start Date] =_max))

refer

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Sum Last Qty = sumx(VALUES(Data1[Cohort]) , [Last Qty])

[JoRo50]

Thank you.  This worked just right.  The artical was perfect, too.  I need to work on phrasing my problems better because I couldn't find that answer when I was searching.

Can you help me understand why my original solution didn't work?  I was able to get the values I needed by cohort, but the sums were off.  In my actual data, MaxDate excluded the unfinished cohort.  But the TestMeasure actually had a total about 8 times higher than it should have been.  I couldn't figure out what it was summing, though.