cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoRo50
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:

CohortMemberReport DateStart DateDays From StartPoints
AAdam9/1/20229/1/202202
AAbbie9/1/20229/1/202201
AAdam9/15/20229/1/2022146
AAbbie9/15/20229/1/2022148
AAdam9/29/20229/1/2022287
AAbbie9/29/20229/1/2022289
BBobby10/1/202210/1/202201
BBen10/1/202210/1/202201
BBobby10/15/202210/1/2022143
BBen10/15/202210/1/2022148
BBobby10/29/202210/1/2022286
BBen10/29/202210/1/20222815
CCathy11/1/202211/1/202202
CCindy11/1/202211/1/202202
CCathy11/15/202211/1/20221410
CCindy11/15/202211/1/20221411

 

 

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

JoRo50_0-1669940611389.png

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:

JoRo50_1-1669941754839.png

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
amitchandak
Super User
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])

View solution in original post

4 REPLIES 4
danextian
Super User
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]
)

danextian_0-1669948883302.png

 






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."
www.linkedin.com/in/danebelarminocpa

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
Super User
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])

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.