Accumulate using variables-addcolumn-Filter

[Aquila]

Given a certain table with the following structure:

Date-Item-Amount-Type
01/01/2016-1234- 100- AA
02/01/2016-1500- 250- BB
04/01/2016-1500- 400- BB
05/01/2016-1500- 300- AA
05/01/2016-1234- 500- BB

You want to totalize by date and by Item. The type "AA" defines that the amount must be subtracted and the type "BB" defines that the amount must be added. I have developed a calculated column that works well, but when I try to develop the measure that does the same, I do not achieve it. Attached the file with the example.

 

[Hui]

I21: =SUMPRODUCT(($D$21:D21=D21)*(IF(($F$21:F21)="AA",-1,1))*($E$21:E21)) Ctrl+Shift+Enter
then Copy down

 

[Aquila]

Thank you very much Hui, but I intend to do it using Dax.

 

[NARAYANK991]

Hi ,

What version of Excel are you using ?

When I try to open your file using Excel 2010 , I get an error message that the file is corrupt and cannot be opened.

Narayan

 

[Aquila]

Hi. I use version 2016.

 

[Chihiro]

Why complicate things? Do column transformation in PowerQuery stage to modify Amount to negative based on type. Then load to Data Model.

It makes it so much easier to do cumulative sum in measure.

=CALCULATE(SUM([Amount]),FILTER(ALL(Sales),Sales[Date]<=MAX(Sales[Date])),VALUES(Sales[Item]))

See attached.

I loaded table to PQ first and added to your model so 'Sales 1' is the actual table used. And I replaced Amount Column in your model, but you could keep both if needed.

 

[r2c2]

@Aquila Interesting problem. I suggest following @Chihiro suggestion for ease of use. But if you insist on DAX alone solution, then you can use below approach.

Add a unique id (index) to your data. If your data is in Excel, simply add a column with ID =ROW(). If you are bringing it from SQL, change the query to include a numeric ID. Alternatively, you can also use the date column, but it doesn't seem unique.

Define [Total Amount] base measure as =sum(data[Amount])

Add two different Total Amounts as below.
Total Amount AA:= CALCULATE([Total Amount], data[Type]="AA")
Total Amount BB:= CALCULATE([Total Amount], data[Type]="BB")

Add the Cumulative Total measure
Cumulative Total:= CALCULATE([Total Amount BB], FILTER(all(data[ID]), data[ID] <= max(data[ID]))) - CALCULATE([Total Amount AA], FILTER(all(data[ID]), data[ID] <= max(data[ID])))

Now, add the Item, ID as row labels and [Cumulative Total] as values. This should give the desired result.

 

[Aquila]

Very,very good.Excellent!!! Thanks for the answer.

 

[Aquila]

Thank you Chihiro!!! Very good contribution.