Find the Next Date a Transaction will need to Happen

[CProco]

I have a calculated table that shows a list of customers and every time we are scheduled to send money to them (each customer gets a schedule when they bocome our customer and they can get money up to 7 times, but they dont have to take any money so a lot of scheduled dates are 1/1/1900)

What I need to find is the next date we need to send each customer money based on there actually being a scheudle funding for the customer (draw_1_scheduled) and that money hasnt already been sent (draw_1_funded)

customer_ID	draw_scheduled	draw_funded
1	1/1/1900	1/1/1900
2	5/16/2021	1/1/1900
3	1/1/1900	1/1/1900
4	5/1/2021	1/1/1900
1	1/1/1900	1/1/1900
2	6/16/2021	1/1/1900
3	1/1/1900	1/1/1900
4	5/16/2021	1/1/1900
1	1/1/1900	1/1/1900
2	7/16/2021	1/1/1900
3	1/1/1900	1/1/1900
4	6/1/2021	1/1/1900
1	1/1/1900	1/1/1900
2	8/16/2021	1/1/1900
3	1/1/1900	1/1/1900
4	6/16/2021	1/1/1900
1	1/1/1900	1/1/1900
2	1/1/1900	1/1/1900
3	1/1/1900	1/1/1900
4	7/1/2021	1/1/1900
1	1/1/1900	1/1/1900
2	1/1/1900	1/1/1900
3	1/1/1900	1/1/1900
4	7/16/2021	1/1/1900
1	1/1/1900	1/1/1900
2	1/1/1900	1/1/1900
3	1/1/1900	1/1/1900
4	1/1/1900	1/1/1900

[v-xulin-mstf]

Hi @CProco

You can try measure as:

Measure = 
CALCULATE(
    MIN('Table'[draw_scheduled]),
    FILTER(
        ALL('Table'),
        'Table'[customer_ID]=MAX('Table'[customer_ID]) &&'Table'[draw_scheduled]>TODAY()
    )
)

 Here is the output:

The pbix is attached.

Best Regards,
Link

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

[CProco]

Thank you for responding! This was about where I was able to get it. The only problem is that the results for 2 should be 5/16/2021 and for 4 should be 5/1/2021 since they are the oldest on the schedule that havent funded per draw_funded

[v-xulin-mstf · Accepted solution]

Hi @CProco

You can try this:

Measure = 
CALCULATE(
    MIN('Table'[draw_scheduled]),
    FILTER(
        ALL('Table'),
        'Table'[customer_ID]=MAX('Table'[customer_ID]) && 'Table'[draw_funded]=MINX('Table','Table'[draw_funded]) && 'Table'[draw_scheduled]<>MINX('Table','Table'[draw_scheduled])
    )
)

Best Regards,
Link

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