locked
DAX measure: show the latest weeks SalesAmount pr product RRS feed

  • Question

  • Hi guys 

    I have a DAX question that I hope you guys can help me with:

    My data looks like this:

    Product Week SalesAmount
    Product1 48 20100
    Product2 48 15900
    Product1 49 25400
    Product2 49 17900

    I would like an end-result that looks like this,
    that shows the latest week and latest sales amount pr product.

    Product LatestWeek LatestSalesAmount
    Product1 49 10400
    Product2 49 14900

    I have the measure for "Latest week":

    LatestWeek:= MAX([Week])

    However i need the measure for LatestSalesAmount. I was thinking something like this, but that is not allowed:

    LatestSalesAmount:= CALCULATE(SUM([SalesAmount]);[Week]=MAX([Week]))

    Thanks!



    • Edited by axa_oatmeal Tuesday, December 20, 2016 7:55 AM
    Tuesday, December 20, 2016 7:49 AM

Answers

  • Your sample data doesn't make sense to me, but pls check this measure and tell if/how it differs from your desired result:

    LatestSalesAmount:=CALCULATE(SUM(Tabelle1[SalesAmount]); FILTER(Tabelle1;Tabelle1[SalesAmount]=MAX(Tabelle1[SalesAmount])))
    
    You need to replace "Tabelle1" with the name of your table.

    It is seen as best practice in DAX that you proceed references to a column with the table name and references to a measure with nothing.


    Imke Feldmann TheBIccountant.com

    Tuesday, December 27, 2016 9:03 PM
    Answerer

All replies

  • Your sample data doesn't make sense to me, but pls check this measure and tell if/how it differs from your desired result:

    LatestSalesAmount:=CALCULATE(SUM(Tabelle1[SalesAmount]); FILTER(Tabelle1;Tabelle1[SalesAmount]=MAX(Tabelle1[SalesAmount])))
    
    You need to replace "Tabelle1" with the name of your table.

    It is seen as best practice in DAX that you proceed references to a column with the table name and references to a measure with nothing.


    Imke Feldmann TheBIccountant.com

    Tuesday, December 27, 2016 9:03 PM
    Answerer
  • Hi axa_oatmeal,

    How do you get the 10400 from your above data? Please use the formula given by Imke, and check if it is fine. If it isn’t, please post details and sample data for further analysis.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 28, 2016 6:01 AM