cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate Group Sum for most recent date available

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

1 ACCEPTED SOLUTION
Super User

@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])

4 REPLIES 4
Super User

Hi @JoRo50 ,

``````Points (latest date) =
SUMX (
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]
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Frequent Visitor

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.

Super User

@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])

Frequent Visitor

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.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors