Earlier() function help

[Anonymous]

Hi I'm wondering if this is possible to do this with the earlier function or something else:

I have two tables with date columns profile date and edited date I need to take the profile date and match it with the matching edit date but if the profile date doesn't have a match then use the last recorded edit date:

table 1

Account number	Profile Date	Current balance
5	07-02-2020	1500
2	07-14-2020	1500
5	07-10-2020	1300
3	07-15-2020	1000

Table 2

Account number	Edited date	Share
5	07-02-2020	1.45
2	07-05-2020	1.65
5	07-02-2020	1.45
3	07-15-2020	1.30

I would like have if both dates match in the same account number preform this equation
current_balence * share = share_balance 

if there is not match between the two tables then use the most recent previous edit date of the account to preform that equation above.

example: account 2 in the above tables date don't match the portfolio date = 07-14-2020

edite date = 07-05-2020

so 1500 * 1.65 = 2,475 share_balance

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you can try this code to create a calculated column in Table1.

Share balance =
VAR _Current_balance = Table1[Current balance]
VAR _Previous_Edited_Date =
    CALCULATE (
        MAX ( Table2[Edited Date] ),
        FILTER ( Table2, Table2[Edited Date] <= EARLIER ( Table1[Profile Date] ) )
    )
VAR _Share =
    CALCULATE (
        DISTINCT ( Table2[Share] ),
        FILTER ( Table2, Table2[Edited Date] = _Previous_Edited_Date )
    )
RETURN
    _Current_balance * _Share

Relationship:

Result is as below.

Best Regards,
Rico Zhou

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