Forum Discussion
Reporting by latest date
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 )
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
)
)
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
)Thank you Zubair_Muhammad, that looks a similar solution, which can be easily adapted to display rows for criteria like first date for customer, last date for customer, min quantity for customer or max quantity for customer.
- CustomerFirstDate = IF ( Salesfile[Date] = CALCULATE ( MIN ( Salesfile[Date] ), ALLEXCEPT ( Salesfile, Salesfile[Customer] ) ), 1 )
- CustomerLastDate = IF ( Salesfile[Date] = CALCULATE ( MAX ( Salesfile[Date] ), ALLEXCEPT ( Salesfile, Salesfile[Customer] ) ), 1 )
- CustomerMinQty = IF ( Salesfile[Quantity] = CALCULATE ( MIN ( Salesfile[Quantity] ), ALLEXCEPT ( Salesfile, Salesfile[Customer] ) ), 1 )
- CustomerMaxQty = IF ( Salesfile[Quantity] = CALCULATE ( MAX ( Salesfile[Quantity] ), ALLEXCEPT ( Salesfile, Salesfile[Customer] ) ), 1 )
Thank you very much for your assistance in the matter, it is very much appreciated, and I hope that this info will help others.
This will and definitely has helped others - me! I greatly appreciate your simplistic approach, as I've been investigating countless examples that don't quite match the logic needed in my report. Much appreciated!