Get latest amount per customer and categorie

[BobKoenen]

Hi all,

I have a table with outstanding Quotations per customer and I want to return to me the last Quotation amount per categorie per customer.

My data looks like this:

Customer	Category	Date	Amount	In Progress
Joe	Icecream	1-1-2021	500	Y
Joe	Icecream	1-2-2021	1000	Y
Jane	Icecream	2-3-2021	600	Y
Jane	Lego	5-5-2021	1000	N
Jill	Lego	1-2-2021	600	Y
Jill	Lego	5-2-2021	5000	N

My desired result looks like this:

Customer	Categorie	Date	Amount	In progress
Joe	Icecream	1-2-2021	1000	Y
Jane	Icecream	2-3-2021	600	Y
Jane	Lego	5-5-2021	1000	N
Jill	Lego	5-2-2021	5000	N

I hope you can help me.

[Jihwan_Kim]

New Table =

SUMMARIZECOLUMNS (

Data[Customer],

Data[Category],

"Date", MAX ( Data[Date] ),

"Amount",

VAR _lastdate =

CALCULATE (

MAX ( Data[Date] ),

FILTER (

Data,

Data[Customer] = MAX ( Data[Customer] )

&& Data[Category] = MAX ( Data[Category] )

)

)

RETURN

CALCULATE ( SUM ( Data[Amount] ), FILTER ( Data, Data[Date] = _lastdate ) )

)

https://www.dropbox.com/s/60qwakxj868f72s/bobkoenen.pbix?dl=0 

[edhans]

Here you go. 

You need 3 measures:

Latest Amount = 
VAR LatestDate = [Latest Date]
RETURN
SUMX(
    FILTER(
        'Table',
        'Table'[Date] = LatestDate

        ),
    'Table'[Amount]
)

Latest Date = MAX('Table'[Date])

Latest Progress = 
VAR LatestDate = [Latest Date]
RETURN
MAXX(
    FILTER(
        'Table',
        'Table'[Date] = LatestDate

        ),
    'Table'[In Progress]
)

My PBIX file is here for you to review @BobKoenen 

EDIT: @BobKoenen - I edited this so this one post would have all of the correct measures, and the PBIX file I linked to is correct. The screenshot is innacurate, but you can see the correct table visual that is created in the PBIX file. If this works, please mark as the solution. If not, please let me know what is not working yet for you. Thanks!

[BobKoenen]

Thanx for your Fast Reply but unfortunatly this is not the Answer. In your example the Amounts Are summed for Jill and Joe. 
The result for Jill should be 5000 and not 5600 for Joe it should be 1000 instead of 1500. I want to return the latest value if customer and categorie are the same, not the sum

[edhans · Accepted solution]

Use this instead. Sorry about that.

Latest Amount = 
VAR LatestDate = [Latest Date]
RETURN
SUMX(
    FILTER(
        'Table',
        'Table'[Date] = LatestDate

        ),
    'Table'[Amount]
)

My pbix above is updated too.

[edhans]

@BobKoenen - did my revised measure above solve the issue for you?