Forum Discussion

earsuyheng's avatar
earsuyheng
Frequent Visitor
7 years ago

Direct Cash Flow from GL with Dax and M Code

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.BalanceAccountCFResult
 1040002212200 - VAT INPUT Cash
2210411524801152482212400 - PREPAID EXPENSES Cash
201040-115248-1152484001100 - ACCOUNT PAYABLE - SUPPLIERS  
 1170-115248-1152481101163 - CASH IN BANK USD Cash 
2011711524801152484001100 - ACCOUNT PAYABLE - SUPPLIERS  
 1200002212200 - VAT INPUT Cash
 12038416003841604401300 - ALLOCATION FOR FA CLEARING  
221200-115248-1152482212400 - PREPAID EXPENSES Cash
231200-268912-2689124001100 - ACCOUNT PAYABLE - SUPPLIERS  
231210-384160-3841604401300 - ALLOCATION FOR FA CLEARING  
 12138416003841603501100 - LAND Cash
 1220-268912-2689121101163 - CASH IN BANK USD Cash 
2312226891202689124001100 - ACCOUNT PAYABLE - SUPPLIERS  
 103  -100CASH IN BANK USDCash 
 103  100INTEREST INCOME WHT Cash
Need Help

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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's avatar
      earsuyheng
      Frequent Visitor
      7 years ago

      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.

       

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

       


      Cash

      Best regards,

      Heng

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        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