Cumulative sum using filtered dates

[JPrince_22]

Hi all

i have a series of Graphs in Power BI which reference two different amount fields, and two date fields. There is an active relationship on one and the measure works fine, however when trying to use the inactive measure the sum is giving me a daily figure, rather than cumulative. 

The measure that is working correctly is as follows:

PledgedAcount = Calculate(
Sum('Servicing Accounts'[PledgeAmount]),
FILTER(
AllSelected('Servicing Accounts'),
'Servicing Accounts'[PledgeDate] <= MAX('Servicing Accounts'[PledgeDate])
))

and the measure returning a daily figure rather than a cumulative total is:

Deposits Received = Var MaxAcc = Max([Account_funded_date]) 
Return(
Caluclate(
Sum([Ledger_Balance]),
AllSelected('Servicing Accounts'),
'Servicing Accounts'[Account_Funded_date] <= MaxAcc, 
UseRelationship('Servicing Accounts'[Account_Funded_date],DateTable[Date])
)
)

And ideas on where i am going wrong?

thanks 

[Anonymous]

The table is a cartesian join of the calendar to itself, then you cleanup the records where the calendar dates are greater than the As-Of dates.  You end up with a table of As-Of dates with a record for every calendar_day that is equal to or prior to the As-Of date.

So Sum({<End=P(calendar_day)>} Value)

[JPrince_22]

Sorry I don't follow, how to I need to edit the measure to solve this? 

[amitchandak]

@JPrince_22 , can mark the user @ for whom this question is. In case you are looking at what I posted you have  to Change Max to MAXX in var in the formula

[JPrince_22]

Hi @amitchandak 

I have edited this as suggested, but still not working. It now reads:

Deposits Received = var MaxAcc = MAXX('Servicing Accounts',[account_funded_date]) Return calculate(sum([ledger_balance]),allselected('Servicing accounts'),'Servicing accounts'[account_funded_date] <=MaxAcc, userelationship('Servicing accounts'[account_funded_date],DateTable[Date]))

[amitchandak · Accepted solution]

Try one of the two.

 Deposits Received = var MaxAcc = MAXX('Servicing Accounts',[account_funded_date]) Return calculate(sum([ledger_balance]),('Servicing accounts'),'Servicing accounts'[account_funded_date] <=MaxAcc, userelationship('Servicing accounts'[account_funded_date],DateTable[Date]))
 
 Deposits Received =  calculate(sum([ledger_balance]), userelationship('Servicing accounts'[account_funded_date],DateTable[Date]))

Can you share sample data and sample output.

[JPrince_22]

hi @amitchandak 

Measuer 2 did the trick, so simple afterall!!

thank you 

[amitchandak]

Try MAXX

Deposits Received =
Var MaxAcc = MaxX('Servicing Accounts', [Account_funded_date])
Return
Caluclate(
Sum([Ledger_Balance]),
AllSelected('Servicing Accounts'),
'Servicing Accounts'[Account_Funded_date] <= MaxAcc,
UseRelationship('Servicing Accounts'[Account_Funded_date],DateTable[Date])
)

[JPrince_22]

Same result unfortunately