Generating an Index based on date and account?

[Anonymous]

Hi there,

Please see the below snapshot. I am trying to create an index based on the account and the date, as you can see the first account becomes active on 1/1/20 goes forward while the second account becomes active on 2/1/20,  i want to solve for the desired output where i am multiplying the amount * the appropriate rate and running it down.

I have been able to generate this individually, meaning one table for each account, but when i try to do it on one table the calculation falls apart. Can anyone provide insight on how to solve this?  I was thinking an index of some sort but wasn't sure?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

I have created a sample for your reference, we can create the calculated columns to work on it.

Index = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Account] = EARLIER ( 'Table'[Account] )
            && 'Table'[date] <= EARLIER ( 'Table'[date] )
    )
)

Am = 
var a = 'Table'[Index]
return
IF('Table'[Amount]=BLANK(),CALCULATE(MAX('Table'[Amount]),FILTER('Table','Table'[Index]<a && 'Table'[Account] = EARLIER('Table'[Account]))),'Table'[Amount])

Future Value = 
VAR ind = 'Table'[Index]
RETURN
    'Table'[Am]
        * CALCULATE (
            PRODUCTX ( 'Table', 1 - 'Table'[Rate] ),
            FILTER (
                'Table',
                'Table'[Index] <= ind
                    && 'Table'[Account] = EARLIER ( 'Table'[Account] )
            )
        )

[Anonymous]

Wow this is amazing!  Thank you!

[Anonymous]

Any help on this team?