Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Generating an Index based on date and account?

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?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

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

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Any help on this team?

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Wow this is amazing!  Thank you!