Sum of values from the last entry date

[CameronS]

Am trying to create a measure which adds the latest values from each site as per the data below.  Have tried a number of different formulas but nothing seems to work.  Note that not all SiteID enter the information each day but looking to include the value from their last entry.

Data

SubmitTime SiteID PTWAIT

20/12/2018	1207	0
23/12/2018	1207	0
24/12/2018	1208	2000
25/12/2018	1208	1500
20/12/2018	1209	129
23/12/2018	1209	129
24/12/2018	1209	129
25/12/2018	1209	129
20/12/2018	1211	1738
23/12/2018	1211	1738
24/12/2018	1211	1738
25/12/2018	1211	1680
20/12/2018	1212	0
23/12/2018	1212	0
24/12/2018	1212	0

Expected Result for PTWAIT is:

3309 based on

SubmitTime SiteID PTWAIT

23/12/2018	1207	0
25/12/2018	1208	1500
25/12/2018	1209	129
25/12/2018	1211	1680
24/12/2018	1212	0

[Anonymous]

@CameronS Please use below measure to get the latest value per site

Measure = 
VAR _maxdate = CALCULATE(MAX('Table'[SubmitTime]),ALLEXCEPT('Table','Table'[SiteID]))
RETURN CALCULATE(SUM('Table'[PTWAIT]),'Table'[SubmitTime]=_maxdate)

If it helps accept the solution. 

[parry2k · Accepted solution]

@CameronS this will do it

Would appreciate Kudos 🙂 if my solution helped.

Measure 3 = 
VAR _maxdate = 
CALCULATE (
    MAX ('Top'[Date] ),
    ALLEXCEPT ( 'Top', 'Top'[SiteID] )
)
RETURN 
CALCULATE (
    SUM ('Top'[PTWAIT] ), 
    KEEPFILTERS ('Top'[Date]=_maxdate )
)

[CameronS]

@parry2k 

Almost works - the result I get is the sum of the entries of the most recent date (appears to be filtered to only the most recent date only) - when using a card visual.

Doesn't appear to sum the latest entry for each SiteID, but rather sums the entries for each SiteID for the most recent date only.

Any suggestions?

[parry2k]

@CameronS can you explain with example screen shot what you mean?

[V-pazhen-msft]

@CameronS 

Result measure = 
var lastentrydate = MAX('Table'[Date])
Return CALCULATE (SUM('Table'[PTWAIT]),ALLEXCEPT('Table','Table'[ID]),'Table'[Date]=lastentrydate)

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[Anonymous]

@CameronS Please try below measure

Measure = 
VAR _maxDate = CALCULATE(MAX('Table'[SubmitTime]),ALLEXCEPT('Table','Table'[SiteId]))
RETURN IF(MAX('Table'[SubmitTime])=_maxDate,SUM('Table'[PTWait]))

[parry2k]

@CameronS here is the output