Forum Discussion

Nun's avatar
Nun
Resolver I
4 years ago
Solved

Difference between values, filtered by customer and decreasing the value available

Hello,

 

I have this problem, please see the table:

Customer num - Inv Am -  Cash available

1                           50           200          

1                           10           200

1                           5             200 

2                           35           500

2                           25            500

2                           40            500

in DAX, I would like to calculate a columns that is the difference between Cash available and Inv Am, filtered based on the customer num, but for each Inv am, decrease the Cash available

Shortly the new column should like this

Customer num - Inv Am -  Cash available -   Diffence

1                           50           200                      150(200-50)   

1                           10           200                      140(150((previous diff)-10)

1                           5             200                      135(140-5)

2                           35           500                      465

2                           25           500                      440

2                           40           500                      400

 

I hope it was clear.

Thank you in advance for your help!

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Nun ;

    First you should add index column in power query, then add a measure.

    1.add index column in power query

    2.add the dax column.

    Column = [Cash available]-CALCULATE(SUM('Table'[Inv Am]),FILTER('Table',[Index]<=EARLIER([Index])&&[Customer num]=EARLIER([Customer num])))

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Nun ;

    First you should add index column in power query, then add a measure.

    1.add index column in power query

    2.add the dax column.

    Column = [Cash available]-CALCULATE(SUM('Table'[Inv Am]),FILTER('Table',[Index]<=EARLIER([Index])&&[Customer num]=EARLIER([Customer num])))

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Why is there no Date column in your dataset?  I am sure it is there in your actual dataset.  Please share that as well.

    • Nun's avatar
      Nun
      Resolver I
      4 years ago

      Ashish_Mathur , yes there is a dataset, Document date. the solution proposed works, but for some reason it is not in order (Column) there is even an invoice number column

      Thanks!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Share the download link of the PBI file and show the expected result there.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Nun ;

    try it.

    Column = [latest]-CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Document date]<=EARLIER('Table'[Document date])))

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.