Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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 |
|
|
Best regards,
Heng
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |