Cumulative signal with userelationship

[dobregon]

Hi,

I have 2 datasets:
1. Calendar

2. Table with cash and dates (with different type of dates like, prepayment date, payment, etc)

So, i can't do a relationhsip with the calendar, becasue the relation will be for many columns in the table of cash

To solve that sum of values for each period or type of date, i have created the following measures and they work fine.

Actual Amount using PrePayment Date = CALCULATE(Cash[Actual Amount], USERELATIONSHIP(Cash(CalendarCash[Date],Cash[Actual PrePayment_Date]))

Actual Amount using Payment Date = CALCULATE(Cash[Actual Amount], USERELATIONSHIP(Cash(CalendarCash[Date],Cash[Actual Payment_Date]))

So i have a tablix matrix with calendar dates, and then use one or the other and they work with the same calendar.

The issue is when i try to do accumulated values from that measure that it seems not work
i have tried and it doesnt work.

SUMX (
    FILTER (
        ALLSELECTED ( Cash[Actual PrePayment_Date] ),
        CDB_CashMovements[Actual PrePayment_Date] <= MAX ( CDB_CashMovements[Actual PrePayment_Date] )
    ),
    CDB_CashMovements[Actual Amount using PrePayment Date]
)

or

CALCULATE (
    Cash[Actual Amount using PrePayment Date],
    FILTER (
        ALLSELECTED ( cash[Actual PrePayment_Date],
        cash[Actual PrePayment_Date] <= MAX ( cash[Actual PrePayment_Date] )
    )
)

but they dont do the accumulated value of cash.

Can someone help me on this?

Regards!

[v-yanjiang-msft · Accepted solution]

Hi @dobregon ,

According to your description, I create a sample.

Cash table:

CalendarCash table:

The two tables have two inactive relationship as you designed. Here's my solution.

1.I create two measure similar to yours.

Actual Amount using Payment Date =
CALCULATE (
    SUM ( Cash[Actual Amount] ),
    USERELATIONSHIP ( Cash[Actual Payment_Date], CalendarCash[Date] )
)

Actual Amount using PrePayment Date =
CALCULATE (
    SUM ( Cash[Actual Amount] ),
    USERELATIONSHIP ( Cash[Actual PrePayment_Date], CalendarCash[Date] )
)

2. I create two accumulate measures.

Accumulate Payment =
IF (
    [Actual Amount using Payment Date] = BLANK (),
    BLANK (),
    SUMX (
        FILTER (
            ALLSELECTED ( 'CalendarCash'[Date] ),
            'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
        ),
        [Actual Amount using Payment Date]
    )
)

Accumulate PrePayment =
IF (
    [Actual Amount using PrePayment Date] = BLANK (),
    BLANK (),
    SUMX (
        FILTER (
            ALLSELECTED ( 'CalendarCash'[Date] ),
            'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
        ),
        [Actual Amount using PrePayment Date]
    )
)

If you want to show all days of the calendar date, you can remove IF statement in the formula, only keep else part. Put Date from CalendarCash table and other measures in a matrix, get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dobregon]

@v-yanjiang-msft  thanks a lot, that works!!!!!