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 AmadiModerator 19 hours 38 minutes ago

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

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 AmadiModerator Wednesday, May 27, 2015 11:28 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,

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

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.

Thanks for the solution,u bring me great way to work.

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.