Accumulated daily interests

[Anonymous]

Hi, I have the following data:

Where the Daily payment = Capital * Daily Rate, and that means the amount of money I need to pay in one day for that client. Ex: For client 1, on Jan 4th I'll have to pay $2.34, on Jan 5th 4.86 and so on.

What I would like is to see the accumulated amount for all clients by a certain date. Something like this:

Ideally, it would be a Measure and not Calculated Column since I would like to filter by clients.

Is there any way to do this? My data has around 30k rows and dates go as far as 2015, so making a new column for each date doesn't sound reasonable.

Any ideas would be really helpful!!

[Zubair_Muhammad]

@Anonymous

Try this MEASURE

Measure =
CALCULATE (
    SUM ( TableName[Daily Payment] ),
    FILTER (
        ALL ( TableName[Starting date] ),
        TableName[Starting date] <= SELECTEDVALUE ( TableName[Starting date] )
    )
)

[Anonymous]

@Zubair_Muhammad

It doesn't seem to work as desired. 

The measure seems to calculate the Daily interest for one given day. Any advice?

Regards,

[Zubair_Muhammad]

@Anonymous

Could you share your file?

See my attached file here

It works when I use a dummy data set.

May be I am missing something

[Anonymous]

@Zubair_Muhammad

This is my sample file:

https://www.dropbox.com/s/egs6ydzfyah66cu/Sample%20accumulated.pbix?dl=0

Regards,

[Zubair_Muhammad · Accepted solution]

@Anonymous

Use this MEASURE in TABLE 1

Cumulative =
SUMX (
    Table1,
    VAR Minvalue =
        MIN ( Table1[Starting date], SELECTEDVALUE ( Table2[Date] ) )
    VAR Maxvalue =
        MAX ( Table1[Starting date], SELECTEDVALUE ( Table2[Date] ) )
    RETURN
        IF (
            SELECTEDVALUE ( Table2[Date] ) >= Table1[Starting date],
            DATEDIFF ( Minvalue, Maxvalue, DAY ) * Table1[Daily payment]
        )
)

[Anonymous]

@Zubair_Muhammad

It worked perfectly, thanks a lot!

Regards,

[Zubair_Muhammad]

@Anonymous

See your updated file attached here

I deleted the relationship between 2 tables becasue it is of no use