Using a filter with no relationship

[dw700d]

I received three deposits in an account. Deposit 1 was 10M , Deposit 2 was 5M  and Deposit 3 was 4.25M.  Anytime a deposit hits the account the entire deposit must be spent within 31 months. The money must be spent in order of receipt so I can’t spend the 5 M from deposit 2 until the 10 M from deposit 1 is spent and I can’t spend the 4.25M from deposit 3 until deposit 2 is spent. I would like to create a gauge visual that allows me to view how much money has been spent. I would like to filter that gauge by deposit

For example the dataset below shows that by 7/30/20 we have spent 260,000 YTD.

The Gauge visual when filtered by Deposit 1 would reflect the actual spend of 260,000 and reflect a target of 10M (this is the entire amount of the deposit from Deposit 1)

When filtered by Deposit 2 it should reflect actual spend of 0 because Deposit 1 hasn’t been depleted yet and reflect a target of 5M (this is the entire amount of the deposit from Deposit 2)

When filtered by Deposit 3 it should reflect actual spend of 0 because Deposit 1 hasn’t been depleted yet and reflect a target of 4.25M (this is the entire amount of the deposit from Deposit 3)

How can I filter spend  based on Deposit 1 2 & 3?

[mahoneypat]

Here is one way to do this.  It assumes there is no relationship between the two tables, but it could be modified if that is the case too.  I added an Index column in query on your Deposits table to be used in the measure, to get the total amount of previous deposits (DepositOrder column).

Spend FIFO =
VAR totalspend =
    SUM ( Spend[Spend] )
VAR thisdeposit =
    IF (
        HASONEVALUE ( Deposits[Deposits] ),
        AVERAGE ( Deposits[DepositOrder] ),
        MAX ( Deposits[DepositOrder] )
    )
VAR thisdepositamount =
    SUM ( Deposits[Amount] )
VAR previousdeposits =
    CALCULATE (
        SUM ( Deposits[Amount] ),
        ALL ( Deposits ),
        Deposits[DepositOrder] < thisdeposit
    )
VAR remainingamount = totalspend - previousdeposits
RETURN
    IF (
        HASONEVALUE ( Deposits[Deposits] ),
        IF ( remainingamount > 0, thisdepositamount - remainingamount, 0 ),
        SUM ( Deposits[Amount] ) - totalspend
    )

I used different data so the #s don't match yours, but here is what it looks like in use.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dw700d]

@mahoneypat  thank you.I created the measure

I then tried to drop the measure into the visual but got the message below. Any thoughts what went wrong?

[mahoneypat]

Make sure all your columns with numbers have a numeric data type.  Also, if not already, you will need to replace the table and column names to the actual ones in your model.

Regards,

Pat

[dw700d]

@mahoneypatthanks that helped but I am still experiencing issues and its probably because I didnt explain my need well enough sorry about that .

Using your data I drop the measure in the gauge visual and it shows -8k  when filtered by Deposit 1. but it should show 10k. It shows -800 when filtered by deposit 2 but it should show 7200 and it shows 8700 when filtered by deposit 3 but it should only show 800. Below is a snapshot of what I described

I want to look at the gauge on a any given day and see how much of the deposit has been spent so for example if I looked at the gauge on  january 1,  I would see 6,000 spent from deposit 1, 0 for deposit 2 and 0 for deposit 3. I would also like my target to adjust based on the the deposit type, so the target for deposit 1 would be 10,000, the total for deposit 2 would be 7200 and the target for depost 3 would be 9500 .Apologies for not being clear

[dw700d]

@Greg_Deckler  Yesterday you suggested my post was not detailed enough. Is this one any clearer?

thanks

[Greg_Deckler]

@dw700d - Yep, my only suggestion would be to post that data as text in a table. Otherwise we all have to hand enter data in order to experiment. Slows things down.