Excel Forum

Power BI - Month End Debtor - Help!

David Barfoot

Active Member

Members

Forum Posts: 5

Member Since:
July 22, 2022

Offline

August 9, 2023 - 9:35 pm

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

Mynda Treacy

Admin

Forum Posts: 4773

Member Since:
July 16, 2010

Offline

August 11, 2023 - 9:08 am

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

David Barfoot

Active Member

Members

Forum Posts: 5

Member Since:
July 22, 2022

Offline

August 11, 2023 - 7:38 pm

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).

Debtor Total Value =

CALCULATE(

SUM('Sales Ledger Trans'[Total Value]),

FILTER(

ALL('Sales Ledger Trans'),

'Sales Ledger Trans'[TransactionDate] <= MAX('Sales Ledger Trans'[TransactionDate]) &&

'Sales Ledger Trans'[AccountNumber] = SELECTEDVALUE('Sales Ledger Trans'[AccountNumber])

)

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

August 14, 2023 - 2:26 pm

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.....tar-schema