Calculate operations after certain value

[Anonymous]

Hi everyone, I have some trouble trying to do an specific topic, here is my data:

ID	Register	Type	Amount
1	01/01/2018	Balance	5
2	01/01/2018	Cash	10
3	01/01/2018	Cash	15
1	02/01/2018	Balance	20
2	02/01/2018	Balance	5
3	02/01/2018	Balance	10
1	04/01/2018	Balance	8
2	04/01/2018	Cash	10
3	04/01/2018	Balance	15
1	05/01/2018	Cash	10
2	05/01/2018	Balance	10
3	05/01/2018	Balance	20

ID: id user

Register: when a user buy an item

Type: type of payment

Amount

When someone wants to buy in the store, we give them an amount of 20 (Balance),the the user can buy some money in cash and add to balance.

I want to calculate the operations after a user spend that money (no matter type of payment). For example, user ID 1, spend the money on 2nd transaction, then he has 2 more transactions.

The result I expect is:

ID	Total Payment	Total Balance payment	Total Operations	OperationsAfter20
1	43	33	4	2
2	35	15	4	0
3	60	45	4	1

Thank you so much guys!

[v-xiaoyan-msft · Accepted solution]

Hi，@Anonymous

You can use the following method to meet your needs:

Create a measure for Cumulative Balance to get OperationsAfter20 like this:

Cumulative Balance =

CALCULATE (

    SUM ( 'Table'[Amount] ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[ID] = MAX ( 'Table'[ID] )

            && 'Table'[Type] = "Balance"

            && 'Table'[Register] <= MAX ( 'Table'[Register] )

    )

)

OperationsAfter20 =

VAR _date =

    CALCULATE (

        MIN ( 'Table'[Register] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID] = MAX ( 'Table'[ID] )

                && [Cumulative Balance] > 20

        )

    )

RETURN

    COUNTROWS (

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID] = MAX ( 'Table'[ID] )

                && [Cumulative Balance] > 20

                && 'Table'[Register] > _date

        )

) + 0

Try to create new measure for total balance payment, Total operations,  Total payment.
Total Balance payment =

CALCULATE(

    SUM('Table'[Amount]),

FILTER('Table','Table'[Type]="Balance"))

Total Operations = COUNT('Table'[Amount])

Total Payment = SUM('Table'[Amount])

Then you will get what you want.

Here is the demo , please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/Ea6D_qeipgJKiXzYPre8TAMByCNtr-XqyvSb3hxxGCl__Q?e=wlt9G6

Hope it helps.

Best Regards,

Caitlyn Yan

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

[amitchandak]

@Anonymous , 4th one is not clear. Try measures like

Total Payment = sum(Table[Amount])

Total Balance payment = calculate(sum(Table[Amount]), filter(Table, Table[Type] ="Balance"))

Total Operations = count(Table[Amount])

OperationsAfter20 = calculate(count(Table[Amount]), filter(Table,Table[Register] > calculate(maxX(filter(Table, Table[Type] ="Balance" && Table[Amount] =20), Table[Register]), allexpcept(Table,Table[place_digital_adress]))))

[Anonymous]

Thanks for your response.

OperationsAfter20: I want the operations after the user spent his first 20 in balance