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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 ,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
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.

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors