Forum Discussion

JBG's avatar
JBG
Regular Visitor
8 years ago
Solved

highest value by category

    Hello everyone!! I am trying to extract the data from the latest date record (ex: for each customer last delivery date & delivered quantitiy) I am able to select the last date but I get...
Need Help
  • GilbertQ's avatar
    GilbertQ
    8 years ago

    Hi there

     

    This is possibly what you are after?

     

    Measure = 
CALCULATE (
    SUM ( 'Table1'[StorageLevelAfterDelivery(kg)] ),
    FILTER ( 'Table1', 'Table1'[ShiftEndDate] = MAX ( 'Table1'[ShiftEndDate] ) )
)

GilbertQ's avatar
GilbertQ
Super User
8 years ago
Hi there,

Could you please post some sample data and the expected outcome so that we can look at how to solve your current challenge?
JBG's avatar
JBG
Regular Visitor
8 years ago

Hello Gilbert,

 

Here is an example of what I would like to do :

SiteNameShiftCodeShiftEndDateStorageLevelAfterDelivery(kg)
A2320/07/201833 600
A2615/12/201734 300
A3008/03/201834 300
A5205/04/201834 300
A3730/01/201835 000
A12316/02/201835 000
B4515/12/201716 800
B8907/08/201832 900
B9515/03/201833 250
B7521/03/201833 250
B7827/02/201833 600
B6229/03/201833 600
C5415/12/201734 300
C6808/03/201834 300
C14505/04/201834 300
C9630/01/201835 000
C10216/02/201835 000
    
SiteNameShiftCodeLatestShiftEndDateStorageLevelAfterDelivery(kg)
A2320/07/201833 600
B8907/08/201832 900
C14505/04/201834 300

Thanks by advance

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi JBG,

     

    Create a measure using DAX as below:

    LatestShiftEndDate = CALCULATE(MAX(Table1[ShiftEndDate]), ALLEXCEPT(Table1, Table1[SiteName]))

      

     

    Regards,

    Jimmy Tao

    • JBG's avatar
      JBG
      Regular Visitor
      8 years ago

      Hi Jimmy,

       

      Thanks for your answer. Unfortunatley, I still have the same problem which is it doesn't filter one data (the latest) per site name. When I wwant to know quantities per site at the latest date, I still have several datas per site... If you have another advice... Thanks

      • GilbertQ's avatar
        GilbertQ
        Super User
        8 years ago

        Hi there

         

        This is possibly what you are after?

         

        Measure = 
CALCULATE (
    SUM ( 'Table1'[StorageLevelAfterDelivery(kg)] ),
    FILTER ( 'Table1', 'Table1'[ShiftEndDate] = MAX ( 'Table1'[ShiftEndDate] ) )
)