How to sum values only once per month

[jaco1951]

Hi

I have a series of loans that 99% of the time have instalments every quarter or 6 months. But a few times there are made instalments twice in the same month.

Are there any good solution to present this graphically without doubling the loan balance for these periods. I would prefer to sum only the last date of instalment, but the aggergation is on YearMonth level, not date level as it would make the table almost 100 times bigger.

Best regards

Espen

[v-shex-msft]

HI @jaco1951,

Is there any conditions to check which records are the duplicated? If you have, you can add this to calculate formula to remove unmatched part.

Regards,
XIaoxin Sheng

[jaco1951]

Hi

Unfortunately these are not duplicates, but extraordinary paymentes outside the payment plan. So they have effect on the loan balance.

What I have done now is changing my granularity from YearMonth to date level, it blows up the size quite a lot, but it might work. I used auto fill to fill the empty dates with loan balance values.

Yet, I would like to learn how this could be solved in a better way.

Best regards Espen

[v-shex-msft · Accepted solution]

HI @jaco1951,

So you mean not calculated summarize on remain loan when tables contains multiple records in same month, right?

I think you can create a variable to manually summarize loan to calculate correct amount, then you can look up correct loan from summary table and use this to calculate.

Display Measure =
VAR summied =
    SUMMARIZE (
        Table,
        [%Sec_CFP],
        [FIrstDate],
        [LoanBalance_Start],
        [LoanBalance_End]
    )
RETURN
    'formula'

Regards,

Xiaoxin Sheng