July 22, 2022
I am trying to produce a visual that shows the month end debtor for all accounts, something like the attached although the values in this are wrong.
I have a table of all sales ledger transactions from the start of time like attached tables view and what I am looking to do is for it to basically sum up all the transactions for the accounts from the start of time until the whatever the selected months in the visual are i.e. until Jan23, Feb23, Mar23, e.t.c. It can't just sum up the transactions in the selected months it needs to be everything from the start of time until the selected months so the debtor amount is correct.
If there are no transactions in a selected month for a debtor but still an os amount from previous month I want to display the os balance.
Is someone able to help me out with some advice for the code for a measure that might do this?
July 16, 2010
July 22, 2022
Thanks very much Mynda, the closest I can get it to working is the below which basically works other than if there is no transactions for an account in a month. If that is the case it returns blank for that account in that month rather than the cumulative total up to the end of the last month in which there was a transaction (i.e. the debtor value should be the same).
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Excel Customer Service
November 8, 2013
Looks like you are not using a dimension table for AccountNumber. If you create it and use it in the visual and formula, it will work.