DAX Recommendation for Calculating the Change in Account Balances

[rsanford]

All:  

For several years, I have been calculating the following information in Excel. Now I would like to transition this into Power Bi.  Using Power BI, I am a little unsure of the correct /most efficiaent manner to calculate the Deferred Revenue and Accounts Receivable balances in column H and L respectively.

Using Excel, the forumla that I created to calculate the amouts are as follows:

Points of Reference:

- Deferred Revenue (DR) is the the amount of money received (collected) prior to it being earned  -- Collections grater than Earnings

- Accounts Receivable (AR) is the amount that the custmer owes -- Earnings greater than Collections

- The Fiscal YEAR in which the activity took place.  This is broken out by the TSYM in COL A

The Excel Formulas are as follows:

1. For the First Year  (Row 3/(Column B)   

For Deferred Revenue - The formula is straight forward   =IF(G3>E3,G3-E3,0)  -- If Collections are Greater than Earnings, then the amount is the difference of the Collected amount verus the Earned Amount.  Otherwise, the amount is $0.00

For Accounts Receivable, the is also stright forward  =IF(H3>0,0,E3-G3)  -- If there is Deferred Revenue, then the AR amount must be $0.00, Otherewise, it is the Total Earned minus the Amount Collected..

2. For the Second Year (Row 4/(Column B)   

The Excel Formulas begin to get complex as we have to look back at the First Year to determine what the balances were:

Like before, we start with the deferred revenue  =IF(E4=G4,H3+0,IF(G4-E4>0,IF(H3>0,H3+(G4-E4),IF(I3>(G4-E4),0,(G4-E4)-I3)),IF(H3>(E4-G4),H3-(E4-G4),0)))  -- If the Earned amount is the same as the Collected amount, then take the previous deferred amount (H3) and add 0.00. Otherwise, we need to assess the amount that we currently collected less the amount we earned (G4-E4) to see if it is greater than 0.00.

If TRUE (G4-E4>0), we need to determine what to do what that amount so we have to look at the previous year to determine if we had Deferred Revenue. 

If there was previous Deferred Revenue (H3>0), then the current DR would be the previous amount plus the difference in the new payments minue new earned (H3+(G4-E4).  

If there was not previous Deferred Revenue (H3), then we look at the previous Accounts Receivable (I3) to see if the amount is greater that new collections minus new earned.. If that is true then current year DR must be equally to 0.00. Otherwise, we calculate the new (current year) DR to be current year collections minus current year earned minus last year accounts receivable (G4-E4)-I3.

If FALSE (G4-E4>0), we need to determine what to do what that amount so we have to look at the previous year to determine if we had Deferred Revenue. 

If the previous Deferred Revenue is greater than the current total earned minus current customer payments (H3>(E4-G4) then the current DR would the previous DR minus the summ of the the current total earned minus current customer payments (H3-(E4-G4))

Otherwise, current year DR would be 0.00.

The Accounts Receivable fromula =IF(H4>0,0,IF(H3>0,E4-G4-H3,I3+(E4-G4))) uses the information in the DE calculation based on the premise that is there is current Deferred Revenue, then we CAN NOT have an Accounts Receivable.

Therefore if the DR amount is greater than 0.00 then the AR amount will be 0.00 (H4>0,0)

Otherwise, begin to evaluate the current Account Receivable amount by first determining if there was previous year DR (H3>0)

If the previous year DR amount was greater than zero, than take the sum of current year Total Earned Revenue minus  Current Year Payments and subtract the amount of prior year deferred revenue (E4-G4-H3)

 If the previous year DR was not greater than zero, than take the the previous year AR plus the sum of the Total Earned Revenue minus Current Year Payments (I3+(E4-G4)

I greately appreciate in advance any recommendations on the best approach as to how I can perfrom these Excel based formulas in DAX. I would like to create Measures so that I can use them in various Reports.

Regards,

Robert

[rbriga]

So, if I understand correctly, you wish for the values we've calculated to aggregate over the fiscal year, but not over the TSYM. That did work for me, though.

How are your date tables connected? Can you show me a diagram (like I did in my post), including the related fields and relationship type and directionality?

[rsanford]

In Actuality, the hierarchy is a follows:

- Agreement

    - TSYM  

       - Year

For purpose of this sample, the Agreement was omitted.

[rbriga]

Hi, 

Power BI (or more accurately, DAX) make the formulas much simpler!

First, I'm going to assume that these are your source tables- you will want a calendar table (in this case, years):

Now, create a relationship between Calendar[Year] and FiscalData[Fiscal Year]- one-to-many:

Now, I've created these 3 measures:

Collection Balance = 
SUM(FiscalData[Customer Payments])-SUM(FiscalData[Total Earned])

Deffered Revenue = 
VAR _BalanceThisYear =CALCULATE([Collection Balance],REMOVEFILTERS('Calendar'),KEEPFILTERS('Calendar'[Year]=MAX('Calendar'[Year])))
VAR _BalancePreviousYears =CALCULATE([Collection Balance],REMOVEFILTERS('Calendar'),KEEPFILTERS('Calendar'[Year]<MAX('Calendar'[Year])))
RETURN
MAX(0,_BalanceThisYear+_BalancePreviousYears)

Accounts Receivable = 
VAR _BalanceThisYear =  CALCULATE([Collection Balance],REMOVEFILTERS('Calendar'),KEEPFILTERS('Calendar'[Year]=MAX('Calendar'[Year])))
VAR _BalancePreviousYears =CALCULATE([Collection Balance],REMOVEFILTERS('Calendar'),KEEPFILTERS('Calendar'[Year]<MAX('Calendar'[Year])))
RETURN
MAX(0,-_BalancePreviousYears-_BalanceThisYear)

And now, 

Add them to a table visual with the year from the calendar table:

Victory!

[rsanford]

rgirga:

Thank you for your response. 

To answer your assumption regarding the the source table - the source actually is a large General Ledger table with calculations of various transactions to determine the Expenditure, In-DIrect, Total Earned and the Customer Payment amounts.

I took the information that you provided and created a Sample pbix file (not sure why I cannot attach it).

However, there were a few questions that I have based on the calculations (measures) that you provided:

1. I created the following Matrix based on the TSYM and the YEAR

Here is what the expected results should look like:

A.  The difference is the calculation of the Accounts Receivable (AR) for the 21/22 | 2022 row where the amount should be $122,678.27 not $19,457.27.  Overall, the Total amount of $122,678.28 is correct.

This led me to look closer at the measures that you created.  I added them into the Matrix so we could see the values.  You created the Variables: Deferred Revenue Balance Current Year and Deferred Revenue Balance Previous Years.  You create the same two measures for Accounts Receivables.  At no time was I ever able to get any amounts in the Current Year Balance accumulator.

I changed some of the Customer Payment Amounts to what what that would do.  The following shows the Results vs the Suspected Results:

For clarification on the Expected Results, the Each column is a subtotal.  The Blue Row is a subtotal of TSYM/Year and the Orange Row is the Total of the Blue Rows.  

The Green filled Amounts for the Deferred Revenue (DR) and the Accounts Recevable (AR) represent the ending balance of the DR and AR respectfully.  Where as the Navy filled Amounts are the total of the Green Rows.  

In summary, the resulting values for the subsequent year is not correct because it did not consider the previous year DR and AR balances.  Referencing the last example, the amount of $77,321.73 is not accurate as the amount should be $168,220.31 (which ties the General Ledger Amount).

I welcome your additional feedback.

Regards,

Robert