Forum Discussion
Helper ICash Flow in Power BI
Hello All -
I am looking for a solution on the best way to show cash flow in PBI. Disregarding AR at the moment, I have the bank balances, AP due totals. I also need to take into account the company codes and currency. I am thinking this needs to be a type of "if statement" solution but not sure. Any ideas helpful. Below is sample data.
If statement piece, if the currency is X and the company code is Y then AP payment comes out of this bank number:
| Company Code | Currency | Bank Payment comes from |
| LOMB | GBP | 123 |
| LOMB | USD | 1234 |
| LOMB | EUR and all others | 12345 |
| KHLT | USD | 123456 |
| KHLT | EUR | 1234566 |
| KHLT | GBP and all others exc. USD and EUR | 12345678 |
AP Data examples:
| Company Code | Due Date | Amount | Currency |
| LOMB | 17.04.2012 | $ (480.00) | GBP |
| LOMB | 17.04.2012 | $ (180.00) | USD |
| LOMB | 17.04.2012 | $ (150.00) | USD |
| LOMB | 06.08.2012 | $ (100.00) | GBP |
| LOMB | 07.12.2012 | $ (95.25) | GBP |
| LOMB | 08.02.2013 | $ 85.00 | USD |
| LOMB | 12.02.2013 | $ (17.27) | GBP |
| LOMB | 20.03.2013 | $ 60.00 | GBP |
| LOMB | 17.04.2013 | $ (2,000.00) | GBP |
| LOMB | 30.04.2013 | $ (125.30) | GBP |
| KHLT | 17.05.2013 | $ (100.00) | GBP |
| KHLT | 17.05.2013 | $ (105.22) | GBP |
| KHLT | 19.07.2013 | $ (88.24) | USD |
| KHLT | 25.07.2013 | $ (12.85) | GBP |
| KHLT | 09.08.2013 | $ (810.00) | GBP |
| KHLT | 07.10.2013 | $ (51.52) | GBP |
| KHLT | 25.10.2013 | $ (81.97) | GBP |
| KHLT | 30.10.2013 | $ (180.00) | GBP |
| KHLT | 01.11.2013 | $ 118.86 | USD |
2 Replies
- amitchandak
Super User
HB2104 , refer these solutions
https://www.youtube.com/watch?v=4dosxfNxR6M
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1Or try new dynamic format string
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/
HB2104Thanks! I don't need to convert currencies, I am trying to basically add a new column in this table that can give me back which bank account each invoice should be paid out of based on company code and currency.
So the first row, is KHLT and currency is USD so in that column highlighted I would want it to put 123456 (based on below table I have in the PBI as well). Hopefully that makes sense. Thanks for the help!
Company Code Currency Bank Payment comes from LOMB GBP 123 LOMB USD 1234 LOMB EUR and all others 12345 KHLT USD 123456 KHLT EUR 1234566
