locked
DAX - last occurrence in a month RRS feed

  • Question

  • I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?

    account revenue revenue_date
    1 100 2010-01-01
    1 200 2010-02-03
    1 300 2010-02-04
    1 1000 2010-06-04
    2 100 2010-02-01
    2 200 2010-03-03
    2 1300 2010-07-04
    2 11000 2010-08-04
    3 2100 2010-01-01
    3 2200 2011-02-03
    3 300 2011-02-04
    3 221000 2011-06-04
    4 222 2010-02-01
    4 2222 2012-03-03

    yaniv012 from M.S.

    Wednesday, May 27, 2015 7:06 AM

Answers

  • Hi Yaniv012,

    According to your description, you need to get the last revenue for each amount on each month, right?

    I have tested it on my local environment, we can calculate the month and year for each revenue date, and then get the last date for each month and each amount, then get the revenue based on the last date and amount.
    =Month([Revenue_Date])&"-"&YEAR([Revenue_Date])
    =CALCULATE(MAX(case0528[Revenue_Date]),ALLEXCEPT(case0528,case0528[Account],case0528[MonthYear]))
    =LOOKUPVALUE(case0528[Revenue],case0528[Revenue_Date],case0528[LastRevenueDate],case0528[Account],case0528[Account])

    https://msdn.microsoft.com/en-us/library/gg492170.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by yaniv012 Thursday, May 28, 2015 12:07 PM
    Thursday, May 28, 2015 3:13 AM
  • try this formula, just need to adopt it according to your datamodel:

    AccountLastRevenuePerMonth:=SUMX(
    VALUES('MyTable'[Account]),
    SUMX( 
        CALCULATETABLE(VALUES('MyTable'[MonthYear])),
        CALCULATE(
            SUM('MyTable'[Revenue]),
            LASTDATE('MyTable'[Revenue_Date]))
    ))

    in case a whole year is selected, it sums up the last value of each month
    in case multiple accounts are selected, it sums up the last value of each month for each account

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by yaniv012 Thursday, May 28, 2015 11:59 AM
    Thursday, May 28, 2015 6:54 AM
    Answerer
  • well, we have two iterations

    1) over the distinct values of 'MyTable'[Account] - being all single accounts
    2) for each account we do another iteration over all the months where data exists for this account 'MyTable'[MonthYear]

    then, for each account and each month we calculate the LASTDATE and use its value for our final summation

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by yaniv012 Tuesday, June 9, 2015 7:57 AM
    Tuesday, June 9, 2015 7:23 AM
    Answerer

All replies

  • Hey yaniv012,

    you need to have a Date-Dimension within your Datamodel. (You can easily use Boyan Penevs Datestream via PowerQuery / PowerPivot). 

    Then you need to build a relationship to this Date-Dimension (to your [Revenue Date]).

    Then you can use saomething CLOSINGBALANCEMONTH() or LASTNONBLANK().

    Check out what the Italian's say to that kind of stuff:

    Here: http://www.daxpatterns.com/cumulative-total/

    Or here: http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

    ________________________________________________

    Please mark helpful posts or answers!

    • Proposed as answer by Michael Amadi Wednesday, May 27, 2015 11:28 AM
    Wednesday, May 27, 2015 8:50 AM
  • Hi Yaniv012,

    According to your description, you need to get the last revenue for each amount on each month, right?

    I have tested it on my local environment, we can calculate the month and year for each revenue date, and then get the last date for each month and each amount, then get the revenue based on the last date and amount.
    =Month([Revenue_Date])&"-"&YEAR([Revenue_Date])
    =CALCULATE(MAX(case0528[Revenue_Date]),ALLEXCEPT(case0528,case0528[Account],case0528[MonthYear]))
    =LOOKUPVALUE(case0528[Revenue],case0528[Revenue_Date],case0528[LastRevenueDate],case0528[Account],case0528[Account])

    https://msdn.microsoft.com/en-us/library/gg492170.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by yaniv012 Thursday, May 28, 2015 12:07 PM
    Thursday, May 28, 2015 3:13 AM
  • try this formula, just need to adopt it according to your datamodel:

    AccountLastRevenuePerMonth:=SUMX(
    VALUES('MyTable'[Account]),
    SUMX( 
        CALCULATETABLE(VALUES('MyTable'[MonthYear])),
        CALCULATE(
            SUM('MyTable'[Revenue]),
            LASTDATE('MyTable'[Revenue_Date]))
    ))

    in case a whole year is selected, it sums up the last value of each month
    in case multiple accounts are selected, it sums up the last value of each month for each account

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by yaniv012 Thursday, May 28, 2015 11:59 AM
    Thursday, May 28, 2015 6:54 AM
    Answerer
  • hi Gerhard

    your amaizing solution is working simply Great!!

    i search and got many answers - but yours is the best .

    i want to thank you for your Efforts , WELL DONE.


    yaniv012 from M.S.

    Thursday, May 28, 2015 12:07 PM
  • Thanks for the solution,u bring me great way to work.

    yaniv012 from M.S.

    Thursday, May 28, 2015 12:35 PM
  • hi again Gerhard , i'm new with dax , and i am traing to understand your formula , can you give me a Short explanation how its work ?  i also tried to write another formula based on yours - that find the  last occurrence of all the accounts  - ( not in groups of month )  and then summarize all of these latest occurrences .

    it looks ease but ..

    best regards.


    yaniv012 from M.S.

    Sunday, May 31, 2015 8:12 AM
  • well, we have two iterations

    1) over the distinct values of 'MyTable'[Account] - being all single accounts
    2) for each account we do another iteration over all the months where data exists for this account 'MyTable'[MonthYear]

    then, for each account and each month we calculate the LASTDATE and use its value for our final summation

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by yaniv012 Tuesday, June 9, 2015 7:57 AM
    Tuesday, June 9, 2015 7:23 AM
    Answerer
  • THANKS Gerhard  ,  as usual , you give me a great help !!

    yaniv012 from M.S.

    Tuesday, June 9, 2015 7:59 AM