Balance Sheet Accumulated

DAX DAX Calculations
1

I have an Accounts transaction file that has columns for Account Code, Accounting Year, Period and Amount.

The accounting Year is a four digit year number and the Accounting period is a two digit number (NOT the calendar month number)

I want to:

For Balance Sheet Codes create a separate table that contains a single unique accumulated total by for each of those combinations of those fields.

The range of codes will typically be for a maximum of three years (last year, this year and next year) and each total needs to contain the accumulated total from the very beginning.

Any help with the DAX code would be appreciated.

2

@PaulBoyes,

Welcome to the forum! Glad to have you here.

To get the best support on the forum, please post a copy of your PBIX file plus a mockup of the output you’re looking for.

Thanks.

  • Brian
3

Hi Brian,
Thank you for the quick reply. I have attached the pbix file. The proposed output can be seen on the Balance Sheet tab.

I am getting all the figures sorted before looking at the layouts.

All of the accounts transactions are in NlTransactionsPosted. The account code is made up of:
Company - 2 digits
Cost Centre - 4 digits
Account Code - 5 digits

The first character of the Account Code signifies the transaction type (See Nominal Code Groups) each group also has a pre determined assignation of debit / credit.

The first four characters of the Account Code signify what we call a “Group Ten” which serves as a tool to produce summary reports rather than every single account code. (See NominalCodeGroupTen).

The system keeps three years of detail being Last Year, This Year and Next Year. Next Year is used so the system can still be used while “This Year” is in the process of year end adjustments etc.

The objective with the balance sheet is to store accumulated totals for every combination of Company, Cost Cente & Account code from Last Year period 00 up to Next Year period 13.

Apologies for being so verbose.

Thanks in advance.

Paul
Accounts.pbix (1.2 MB)

4

Hello PaulBoyes,

Thank you for the description. Hopefully I understood what you were after. I tried to make a accumulated formula. See attached pbix.

Accounts.pbix (1.2 MB)

Formula:

Accumulated Transactions = CALCULATE(SUM(NlTransactionsPosted[Amount]);FILTER(ALL(PeriodTable);PeriodTable[PeriodKey] <= MAX(PeriodTable[PeriodKey])))

I have added a separate table for your custom reporting periods. I made it with DAX but I would recommend creating this based on your facts.

Best
Kristoffer

1 Like
5

Hi Kristoffer

Thank you very much, greatly appreciated.

I have not been an Enterprise DNA member for long but I am hugely impressed so far.

Best regards

Paul

1 Like
6

Hi @PaulBoyes, Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Also, please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’, Thanks!

7

Hi
I attached a pbix to my message below. I attach a new copy here.

Regards
Paul
Accounts.pbix (1.3 MB)