# DAX - last occurrence in a month

• ### 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

• 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 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 Thursday, May 28, 2015 11:59 AM
Thursday, May 28, 2015 6:54 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 Tuesday, June 9, 2015 7:57 AM
Tuesday, June 9, 2015 7:23 AM

### 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/

________________________________________________

• Proposed as answer by 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 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 Thursday, May 28, 2015 11:59 AM
Thursday, May 28, 2015 6:54 AM
• 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 Tuesday, June 9, 2015 7:57 AM
Tuesday, June 9, 2015 7:23 AM
• THANKS Gerhard  ,  as usual , you give me a great help !!

yaniv012 from M.S.

Tuesday, June 9, 2015 7:59 AM