Latest data & sum of data

[alexburn]

I have a table of data that has multiple entries per "Job".

I need to display a summary table that shows the latest entry for "Budget" but a sum of entries for "WIP".

Example data below:

Date----------	Job---------	Budget---------	WIP---------
1/1/21	11111	10,000	0
2/1/21	11111	10,000	10
3/1/21	11111	9,500	-5
1/1/21	22222	5,000	0
2/1/21	22222	6,000	20
2/1/21	33333	12,000	0
3/1/21	33333	11,000	-20

Ultimately, I want to summarise this as follows (with red columns being Measurements):

Job---------	Latest Date---------	Latest Budget---------	Sum of WIP---------
11111	3/1/21	9,500	5
22222	2/1/21	6,000	20
33333	3/1/21	11,000	-20

Any assistance would be appreciated, thank you.

[DataInsights · Accepted solution]

Hi @alexburn,

Try these measures:

Latest Date = IF (HASONEVALUE ( Jobs[Job] ), MAX ( Jobs[Date] ) )

Latest Budget Calc = 
VAR vMaxDate = [Latest Date]
VAR vResult =
    CALCULATE ( MAX ( Jobs[Budget] ), Jobs[Date] = vMaxDate )
RETURN
    vResult

Latest Budget = 
VAR vTable =
    ADDCOLUMNS ( VALUES ( Jobs[Job] ), "@LatestBudget", [Latest Budget Calc] )
VAR vResult =
    SUMX ( vTable, [@LatestBudget] )
RETURN
    vResult

Sum of WIP = SUM ( Jobs[WIP] )

[alexburn]

Many thanks @DataInsights 

Having just tried these measures, they seem to work exactly as required!

I'll continue to play around, but this does look like the exact solution I was looking for.

Many thanks!!!!