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?
Many thanks
Hi David,
If I understand correctly, you want a running total. See this tutorial on running totals in Power Pivot.
You could also try using the Quick Measures for running totals.
Mynda
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).
Hi David,
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.
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
