Forum Discussion
Reporting by latest date
A Sales File has 4 fields Customer, Product, Quantity & Date.
I know how to list Customer, Product and Latest Date. That is easy.
Please explain how to produce a report using BI (not excel) of Customer, Product and Date where the date = the latest date for each customer.
There are several forum threads about latest date but none seem to explain this common type of query clearly.
Thank you
| SALES FILE | |||
| Customer | Product | Quantity | Date |
| Gill | Laptop | 28 | 11-Jan-18 |
| Gill | Radio | 31 | 05-Feb-18 |
| Gill | Phone | 25 | 10-Feb-18 |
| John | Washing Machine | 19 | 16-Jan-18 |
| John | Car | 25 | 26-Jan-18 |
| John | Radio | 34 | 05-Feb-18 |
| Mary | TV | 28 | 01-Jan-18 |
| Mary | Kettle | 16 | 11-Jan-18 |
| Mary | Radio | 19 | 10-Feb-18 |
| WANTED REPORT | |||
| Customer | Product | Date | |
| Gill | Phone | 10-Feb-18 | |
| John | Radio | 05-Feb-18 | |
| Mary | Radio | 10-Feb-18 |
HI Ramps
Sorry I was away for a while
Another way could be to add a calculated column to identify the Last Date. Then you can filter by that
LastDate = IF ( TableName[Date] = CALCULATE ( MAX ( TableName[Date] ), ALLEXCEPT ( TableName, TableName[Customer] ) ), 1 )
7 Replies
HI Ramps
Please try these MEASURES
LatestDate = max(TableName[Date])
Latest Product = VAR mydate = [LatestDate] RETURN CALCULATE ( LASTNONBLANK ( TableName[Product], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Customer] ), TableName[Date] = mydate ) )Thank you Zubair_Muhammad for the quick reply.
I have managed to replicate that OK. Thank you very much indeed !
It seems a very complicated solution compared to other query languages.
Do you or anyone else know simpler ways using Power BI please?
I expected a very clear and straight forward filter feature with lots of common options like
- where xxxxx = latest for zzzzz
- where xxxxx = largest for zzzzz
- “where date = latest date for customer”
- or “where quantity = largest quantity for customer”
- or “where date = latest date for product”
The solution you suggested does work but is complicated.
Presumably if the report needed the Customer, Product, Quantity, Date and other fields where the date = the latest date for each customer then a measure have to be added for each column like so:-
LatestDate = max(Salesfile[Date])
Latest Product =
VAR mydate = [LatestDate]
RETURN
CALCULATE (
LASTNONBLANK ( Salesfile[Product], 1 ),
FILTER (
ALLEXCEPT ( Salesfile, Salesfile[Customer] ),
Salesfile[Date] = mydate
)
)
Latest Quantity =
VAR mydate = [LatestDate]
RETURN
CALCULATE (
LASTNONBLANK ( Salesfile[Quantity], 1 ),
FILTER (
ALLEXCEPT ( Salesfile, Salesfile[Customer] ),
Salesfile[Date] = mydate
)
)
