Direct Cash Flow from GL with Dax and M Code

[earsuyheng]

Dear All,

I want to produce direct cash flow from General Ledger and I would like to request dax code and power query m code for generating the cash flow in Result Column as per table below:

1-if one of the double entry contain cash then I want the result column to be Cash based on CF Column.

for example trans. No 103.

2-if first the transaction 104 record payable against expense and subesequently the transaction 117 is to clear payable against cash that reference by Last Recon. No then I want the result column as shown in this table.

Really appreciate for your kind help!

Regards,

Heng

Last Recon. No.	Trans. No.	Dr.	Cr.	Balance	Account	CF	Result
	104	0	0	0	2212200 - VAT INPUT		Cash
22	104	115248	0	115248	2212400 - PREPAID EXPENSES		Cash
20	104	0	-115248	-115248	4001100 - ACCOUNT PAYABLE - SUPPLIERS
	117	0	-115248	-115248	1101163 - CASH IN BANK USD	Cash
20	117	115248	0	115248	4001100 - ACCOUNT PAYABLE - SUPPLIERS
	120	0	0	0	2212200 - VAT INPUT		Cash
	120	384160	0	384160	4401300 - ALLOCATION FOR FA CLEARING
22	120	0	-115248	-115248	2212400 - PREPAID EXPENSES		Cash
23	120	0	-268912	-268912	4001100 - ACCOUNT PAYABLE - SUPPLIERS
23	121	0	-384160	-384160	4401300 - ALLOCATION FOR FA CLEARING
	121	384160	0	384160	3501100 - LAND		Cash
	122	0	-268912	-268912	1101163 - CASH IN BANK USD	Cash
23	122	268912	0	268912	4001100 - ACCOUNT PAYABLE - SUPPLIERS
	103			-100	CASH IN BANK USD	Cash
	103			100	INTEREST INCOME WHT		Cash

[v-danhe-msft]

Hi @earsuyheng ,

I could not understand what your logic of your result column:

1-if one of the double entry contain cash then I want the result column to be Cash based on Trans.No and Lst Recon. No. Column.

for example trans. No 103.

With the No.103, the last recon.no is null, I could not figure out how to based on this column?

Could you please offer me more information about it?

Regards,

Daniel He

[earsuyheng]

Dear Daniel,

Sorry may be make you confuse.

What I mean is when the transaction like No.103 is recorded agianst the bank then I want the result column to appear as cash

under expense account

for example: Tran. No 103.

103	1101162 - CASH IN BANK USD - CANADIA SAVING INCOME - 1169
103	8081200 - INTEREST INCOME WHT	Cash

Best regards,

Heng

[v-danhe-msft]

Hi @earsuyheng ,

Sorry, I still could not figure out how the logic of your result column, like No.120, all of the transaction is 0, the result is 'Cash'.

No.117 is blank, could you please offer more information?

Regards,

Daniel He