Sum of Gross Values based on the max submission date for each App ID to show in a KPI Card

[kalkhudary]

Hi Experts, 

I have daxed the below to be able to get the sum of the gross values where the latest submission date is picked for each application ID and summed with the others. This seems to pick for me only the gross value with the latest date based on all applications. The desire value needs to be 15,214.13 and right now it is showing me 1,982.33

I need to have the value in a visual card as a KPI . Not concerned with a slicer or anything. Unfortunately, I am not able to attach the powerBi file but I am attaching a snip of the Page visuals.

$Gross Value =

VAR _LatestSubmissionDate = ALLSELECTED('Table'[Submission Date])

VAR MaxSubmissionDate= CALCULATE(MAX('Table'[Submission Date]),VALUES('Table'[portalapplicationclaimid]),REMOVEFILTERS('Table'),_LatestSubmissionDate)

VAR ActiveValue =CALCULATE(SUM( 'Table'[totaldisbursement_base]),

FILTER('Table', 'Table'[statuscode] IN {"In Review", "In Review - Manager", "Submitted","Approved", "Opted Out"}), 'Table'[Submission Date]=MaxSubmissionDate)

VAR RESULT = IF(ActiveValue=BLANK(),0,ActiveValue)

RETURN

RESULT

How can I adjust this DAX to read properly? Not sure what I am missing or adding to the dax that it is ot reading properly.

[kalkhudary]

Thanks so much. That worked perfect!

[kalkhudary]

The interesting part is that the dax logic is applying to looking at the lastest submission date for the applicationid but when it is summing the gross value, it does sum based on the max submisison date of all the application IDs (9/11/2023). Any thoughts abotu this dilema.

[v-zhangti · Accepted solution]

Hi, @kalkhudary 

You can try the following methods.

Max Date = 
CALCULATE(MAX('Table'[Submission Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),[statuscode] in {"In Review", "In Review - Manager", "Submitted","Approved", "Opted Out"}))

Measure = 
Var _table=SUMMARIZE('Table','Table'[ID],'Table'[statuscode],"Max Date",[Max Date],"Sum",CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Submission Date]=[Max Date])))
Return
SUMX(_table,[Sum])

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.