cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CProco
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_IDdraw_scheduleddraw_funded
11/1/19001/1/1900
25/16/20211/1/1900
31/1/19001/1/1900
45/1/20211/1/1900
11/1/19001/1/1900
26/16/20211/1/1900
31/1/19001/1/1900
45/16/20211/1/1900
11/1/19001/1/1900
27/16/20211/1/1900
31/1/19001/1/1900
46/1/20211/1/1900
11/1/19001/1/1900
28/16/20211/1/1900
31/1/19001/1/1900
46/16/20211/1/1900
11/1/19001/1/1900
21/1/19001/1/1900
31/1/19001/1/1900
47/1/20211/1/1900
11/1/19001/1/1900
21/1/19001/1/1900
31/1/19001/1/1900
47/16/20211/1/1900
11/1/19001/1/1900
21/1/19001/1/1900
31/1/19001/1/1900
41/1/19001/1/1900
1 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.

 

View solution in original post

3 REPLIES 3
v-xulin-mstf
Community Support
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:

v-xulin-mstf_0-1621404953262.png

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.

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

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.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors