Forum Discussion

JPrince_22's avatar
JPrince_22
Helper I
6 years ago
Solved

Cumulative sum using filtered dates

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 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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.

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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])
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      JPrince_22
      Helper I
      6 years ago

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

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        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