cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Get latest amount per customer and categorie

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.

1 ACCEPTED SOLUTION
Super User

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

),
'Table'[Amount]
)``````

My pbix above is updated too.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
5 REPLIES 5
Super User

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 ) )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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!

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper IV

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

Super User

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

),
'Table'[Amount]
)``````

My pbix above is updated too.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

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

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.