cancel
Showing results for
Did you mean:
New Member

## Find the Next Date a Transaction will need to Happen

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
1 ACCEPTED SOLUTION
Community Support

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,

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

3 REPLIES 3
Community Support

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,

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

New Member

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

Community Support

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,

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

Announcements