Get the date where the sum of future booking amount is not more than the remaining balance

[casperlow]

Hi Dax Guru's any help would be highly appreciated.

I'm trying to do a measure to get the date where the sum of the future booking amount is not more than the remaining balance. 

I have 4 tables = 2 fact table (Future visits (Future Booking services) and transaction (services that have been billed)) and 2 table (service booking and customer).

They are all related by primary keys and foregn keys.

Here's my measure so far.

Predicted Over Delivery Date =

VAR FutureAmount = SUM('Future Visits'[Bill Amount])

VAR remainingBalance = [ROD Remaining Amount] --a calculation that takes the difference between their allocated budget and spent amount.

RETURN

CALCULATE(MAX('Future Visits'[end_date]),FutureAmount<'Measure'[ROD Remaining Amount])

This the result i'm getting.
"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Hoping someone can help me out! 🙂

[Icey · Accepted solution]

Hi @casperlow ,

Have you tried this?

Predicted Over Delivery Date =
VAR remainingBalance = [ROD Remaining Amount]
RETURN
    CALCULATE (
        MAX ( 'Future Visits'[end_date] ),
        FILTER ( 'Future Visits', 'Future Visits'[Bill Amount] < remainingBalance )
    )

Best Regards,

Icey

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

[ValtteriN]

Hi,

In this kind of case you should use a FILTER to remove the error:

CALCULATE(MAX('Future Visits'[end_date]),FILTER(FutureAmount<'Measure'[ROD Remaining Amount]))

This should work and if it doesn't try switching FutureAmount from variable to SUM('Future Visits'[Bill Amount]).


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[casperlow]

Hi,

Thanks for suggesting that, I've tried the filter funtion but it does not generate a date for a person with multiple different booking dates, it only works for a person that have one booking date. 

[casperlow]

@amitchandak are you able to assist on this? many thanks