Dax for calculating sum of all records with Max date

[Tlotly]

Good day

I have dataset with Group Nos, some have multiple meeting dates and some don't have, see example below. The groups all belong to one Centre. From the dates I needed to extract for each Group, a maximum date then use that in a measure to get a sum for a Centre. My desired answer is R8 460, which is all the highlighted groups in Red. 

To achieve this, I first created a column using DAX below to get the maximum dates for each group, which returns corrects results:

Max Meeting Date =

                                  var centre = CentreRepayments[centre code]

                                   var group1 = CentreRepayments[group no]

                                   var meetingperiod = CentreRepayments[Meeting Period]

var __result =

MAXX(

filter(

CentreRepayments,

CentreRepayments[Meeting Period] = meetingperiod

&&

CentreRepayments[group no] = group1

),

CentreRepayments[meeting_process_date]

)

return

__result

Then I used this column in a measure to sum all the Balances with max dates. This works perfectly at a Group level. 

The problem I'm facing is at a Centre Level, I can't get the correct Sum results. See DAX below. The reason being, Max date at a centre level is 30/06/2022. So the measure returns R 3010 which is for Groups A595 and A617. How do I correct this measure to also look at the other groups max dates?

monthlySavingsBal =

                                var maxmeetingdate = max(CentreRepayments[Max Meeting Date])

                                var minmeetingdate = min(CentreRepayments[Max Meeting Date])

                               var savings_bal = CALCULATE(SUM(CentreRepayments[group savings balance]),

FILTER(CentreRepayments,CentreRepayments[meeting_process_date] = maxmeetingdate))

return savings_bal

Any help will be highly appreciated.

Thank you

[Tlotly · Accepted solution]

@amitchandak  Thank you for the quick response. The measures are still not working.

So I found a solution. I added a Flag column on the dataset:

MaxDateFlag = IF(CentreRepayments[meeting_process_date] = CentreRepayments[Max Meeting Date], "Y", "N")

Then used this on the measure. It's working perfectly:

monthlySavingsBal = CALCULATE(SUM(CentreRepayments[group savings balance]),

              FILTER(CentreRepayments,CentreRepayments[MaxDateFlag]= "Y"))

Thank you

[amitchandak]

@Tlotly , Thanks for updating. Good that it is solved. Kudos to you.

[amitchandak]

@Tlotly , try like

New meausre =
var _max = maxx(filter(allselected(CentreRepayments), CentreRepayments[centre code] = max(CentreRepayments[centre code])
&& CentreRepayments[group no] = max(CentreRepayments[group no]) ), CentreRepayments[Meeting_progress Date])
return
calculate(sum(CentreRepayments[Group Saving Balance]), filter(allselected(CentreRepayments), CentreRepayments[centre code] = max(CentreRepayments[centre code])
&& CentreRepayments[group no] = max(CentreRepayments[group no]) && CentreRepayments[Meeting_progress Date] = _max) )

or

New meausre =
var _max = maxx(filter(allselected(CentreRepayments), CentreRepayments[centre code] = max(CentreRepayments[centre code])
&& CentreRepayments[group no] = max(CentreRepayments[group no]) ), CentreRepayments[Meeting_progress Date])
return
calculate(sum(CentreRepayments[Group Saving Balance]), filter(CentreRepayments,CentreRepayments[Meeting_progress Date] = _max) )