DAX measure show the latest weeks SalesAmount pr product
Results 1 to 4 of 4

Thread: DAX measure show the latest weeks SalesAmount pr product
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX measure show the latest weeks SalesAmount pr product

    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 47 15900
    Product1 49 10400
    Product2 48 14900
    I would like an end-result that looks like this, that shows the latest week and latest weeks sales amount pr product.

    So if i for instance have two different products, product1 and product2 my endresult would look like this:

    Product LatestWeek LatestSalesAmount
    Product1 49 10400
    Product2 48 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!

  2. #2
    New Member
    Join Date
    Dec 2016
    Location
    GTA, Ontario
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX measure show the latest weeks SalesAmount pr product

    One way to do it is to add Calculated column.

    "LatestWeek" =IF([Week]=Calculate(Max([Week]),FILTER(Table1,[Product]=EARLIER([Product]))),"Yes","No")

    Then just use that column as Report Filter or Slicer and filter for "Yes".

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX measure show the latest weeks SalesAmount pr product

    Try this - create measure:

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

    and in PivotTable:

    ROWS: Product
    Σ VALUES: LatestWeek, LatestSalesAmount

  4. #4
    New Member
    Join Date
    Nov 2014
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX measure show the latest weeks SalesAmount pr product

    Hi guys

    Thank you very much.

    I ended up using LatestSalesAmount:=CALCULATE(MAX([SalesAmount]);FILTER(Table1;[Week]=MAX([Week])))
    and It works like a charm

    Thanks again and merry christmas

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •