Hi All, I am trying to do the following and I'm fairly new to Power BI and have tried searching, through the forums, but don't seem to be able to articulate what I am searching for properly. I have a table visualisation which lists the sales for each salesperson and the report date. I would only like to show the latest report date for reach person. I.e. from the following data, I would like to select the rows with an X as they are the most recent reports for each salesperson. Name Report Date Product A SalesProduct B SalesProduct C Sales MarcoJun-2023352788XSusanMay-2023482137XSusanApr-2023841613 OliviaApr-2023551245XMarcoMar-2023123065 SusanMar-2023622022 OliviaMar-2023231150 To end up with this:Name Report Date Product A SalesProduct B SalesProduct C SalesMarcoJun-2023352788SusanMay-2023482137OliviaApr-2023551245 Sales staff don't necessarily submit a report every month, so I can't just get the Top 3 reports. Can anyone assist or even recommend a command I should be looking at? I am at a bit of a loss of what to do! Thanks!

Hi rayray661 To achieve your goal follow these steps:1. in your visual table, aggregate report date to latest :2. use the following Dax formula for products sales :sales A = CALCULATE(sum('Table'[Product A Sales]),FILTER('Table','Table'[Report Date ]=max('Table'[Report Date ])))Link to the sample file If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

rayray661

New Member

3 years ago

Solved

Get only the latest entry for distinct column value

Hi All,

I am trying to do the following and I'm fairly new to Power BI and have tried searching, through the forums, but don't seem to be able to articulate what I am searching for properly.

I have a table visualisation which lists the sales for each salesperson and the report date. I would only like to show the latest report date for reach person.

I.e. from the following data, I would like to select the rows with an X as they are the most recent reports for each salesperson.

Name	Report Date	Product A Sales	Product B Sales	Product C Sales
Marco	Jun-2023	35	27	88	X
Susan	May-2023	48	21	37	X
Susan	Apr-2023	84	16	13
Olivia	Apr-2023	55	12	45	X
Marco	Mar-2023	12	30	65
Susan	Mar-2023	62	20	22
Olivia	Mar-2023	23	11	50

To end up with this:

Name	Report Date	Product A Sales	Product B Sales	Product C Sales
Marco	Jun-2023	35	27	88
Susan	May-2023	48	21	37
Olivia	Apr-2023	55	12	45

Sales staff don't necessarily submit a report every month, so I can't just get the Top 3 reports.

Can anyone assist or even recommend a command I should be looking at? I am at a bit of a loss of what to do!

Thanks!

Need Help

Ritaf1983
3 years ago
Hi rayray661
To achieve your goal follow these steps:
1. in your visual table, aggregate report date to latest :
2. use the following Dax formula for products sales :
sales A = CALCULATE(sum('Table'[Product A Sales]),FILTER('Table','Table'[Report Date ]=max('Table'[Report Date ])))
Link to the sample file
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Forum Discussion

Get only the latest entry for distinct column value

3 Replies

Related content

Distinctive Count across two columns

latest date column needed

distinct count of multiple columns

Count distinct values in one column, grouped by another column

Getting distinct values for multiple columns