Resolving Running Sum problem using DAX from Tally

  The given product helps resolve the following problems with regards to valuations across large number of ERP installations.

Attached videos explains the demo:

Copy of the demo:

https://youtu.be/5XIlNLABCIY

The video below gives the following:

Testing the collections and linking the dashboards to the cloud:

https://www.youtube.com/watch?v=1k2IZLRtuhw&feature=youtu.be

Powerbi app  trial login:

Username: guest@mndatasolutions.com.microsoft.com

Password: Bhaiya80$

Download of the sample dashboards-through dropbox:

Dropbox login: powerbimnnbitrial@gmail.com

Password: Bhaiya78$

Client problem: The end client is looking for perpetual inventory and ledger valuations on the date and year month granularity.  This is required for calculating the perpetual gross profit and the perpetual trial balance for the end client.

The issue to be resolved is as follows:

Problems to be resolved for the end client:

a.) Get right inventory valuations by weighted average method for the end client. this shall impact the closing stock value for the end client.

b.) Find the historical accounts receivable ageing for the ledgers at various levels of granularity.  the granularity in the current extract is on daily and year-month basis.

To calculate the running sum in sql or the other languages like TDL is quite complicated in terms of the code. Hence the developer has used the Powerbi DAX coding for simplifying the coding process.

One normally gets the ledger balanaces as a snapshot from various ERPs like Tally( as given below):

However there is a requirement to do the ledger analysis on the basis of dynamic time entries. That is the time framework is not a snapshot but dynamic. 

In order to get the ledger balances in a dynamic fashion one needs to combine the opening ledger balance along with the historical ledger entries

The opening ledger balance is obtained from the ledger snapshot as shown below:

The historical ledger entries is obtained by walking the voucher across allledger entries as shown below:

The issue is that the snapshot and the historical ledger entries need to be combined to get the consolidated running ledger entries.

Thereafter consolidation needs to be done based on time intelligence aggregates.

In order to do the consolidation one needs to either do a join or develop a custom function for walking the collection.

The above process is replaced by DAX code. This DAX code shall ultimately be executed in the cloud.

The method of aggregation is to combine ledger snapshot(with filter conditions for the parent ledger type been "Sundry Debtors" and "Sundry Creditors") and the voucher-allledgerentries to be filtered by parent vouchertypename as "Sales", "Journal" and "Receipt". The value of total credit minus the total debit is the net amount for the given fiscal date. This is then combined with the opening ledger entries to give the consolidated ledger balances.

The join conditions entails joining between a snapshot(Ledger) entity and a cumulative(Voucher-allledgerentries) entity.

The running total is calculated in DAX using the cumulative entries and the date dimensions.

Other dashboards for the product.

This set of dashboards gives the comprehensive Profit and loss analysis based on the dynamic date filter.

The clients should get gross profit, net profit, cost of goods sold, revenue aggregates at various levels of heirarchy(year to date, month to date and quarter to date). Also the clients shall get store wise sales numbers at various time granularities.

Historical inventory analysis; this dashboard gives the inventory profitabiluty analysis at voucher-allinventoryentries level. The granularity of analysis could year, year-quarter and year month. The clients  shall get profitability analysis by items.

The above dashboard is ledger wise profit and analysis sales dashboard. There are readymade objects for sales, gross profit, net profit and cost of sales.

Accounts receivable analysis: this gives the analysis of accounts receivable by ledger snapshot and ledger historical entries.

In case one desires top buy the product then the following are the payment details:

Payment details: