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

[Nun]

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!

[v-yalanwu-msft]

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.

[Ashish_Mathur]

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]

@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]

Hi,

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

[Nun]

Hi, 

as suggested by @v-yalanwu-msft, I created an index column and then a Dax column.

The expected result is in "Column" but from the attachment you can notice it is not in order. Somehow should be filtered even based on document nr or/and document date, recaping; on the first row, column should 1 000 000 - 44 741,18=955 258,82.                                          Second row 955 258,82 - 80 750,15 = 874 508,67. Each customer has several invoices.

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Document Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract year, Month name and Month number.  Sort the Month name column by the Month number.  To your Table/matrix visual, drag Year and Month name from the Calendar Table.  My assumption is that "Amount in Eur" and "latest" are explicit measures that you have written.  Write these meaures:

Amount in EUR YTD = calculate([Amount in EUR],datesytd(calendar[date],"31/12"))

Balance = [latest]-[Amount in EUR YTD]

Hope this helps.

[v-yalanwu-msft · Accepted solution]

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.