PO Report with (+-) condition

[saranp780]

I have a suite of example data below. That  I got for the system.

Purch.Doc.	Item	MatYr	Pstng Date	Quantity	Amount in LC	Crcy	D/C
1020000002	1	2017	28.08.2017	150	27,750.00	USD	S
1020000002	1	2017	28.08.2017	150	27,750.00	USD	H
1020000002	1	2017	01.08.2017	150	27,750.00	USD	S
1020000003	1	2018	05.01.2018	200	18,000	USD	S
1020000003	2	2018	05.01.2018	300	21,300	USD	S
1020000003	1	2018	05.04.2018	50	4,500	USD	H
1020000003	1	2018	08.04.2018	25	2,250	USD	S
Please Note	S	Debit
	H	Credit

Now, What I want to do is make a cumulative report using Debit and Credit to calculate. 

Just so you know about this data.

Posting date means the day of PO makes that move.

D/C Means Debit/Credit in the system it uses S instead Debit and H instead debit.

And when H the amount will be decreased, if S the amount will be Increased.

From my example data, the result will be like this.

Year 2017

Purch.Doc.

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Total

1020000002

27,750.00

27,750 (27,750-27,750+27,750)

Year 2018

1020000003

39,300

4,500

2,250

37,050 (39,300-4,500+2,250)

Please help to tell me the DAX that I can use to solve my requirement.

Thank you all.

[v-huizhn-msft · Accepted solution]

Hi @saranp780,

Please follow the steps below.

1. Create a calendar table clicking 'New Table' under Modeling on Home page, type the formula. Create a month column.

Calendar = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))
Column = FORMAT('Calendar'[Date],"MMM")

2. Create a relationship between your fact table(1) and Calendar table(n).



3. Create a calculated column using the formula.

Amount_new = IF(Table2[D/C]="S",Table2[   Amount in LC],-Table2[   Amount in LC])

4. Create a matrix, select MatYr and Purch.Doc as rows level, the Month as column, then click the button highlighted in yellow, you will get the right result.


Best Regards,
Angelia