Forum Discussion

Ramps's avatar
Ramps
Helper I
8 years ago
Solved

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 Cust...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
)
Ramps's avatar
Ramps
Helper I
8 years ago

 

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!