Balance Sheet Reporting Error - Time Intelligence Calculation Not Working, Power BI

DAX DAX Calculations
1

Hi All,

I am in building on to the Financial Reporting Example you used in the Demo by adding on Balance Sheet line items. I am stuck with the DAX formula to calculate the values. Obviously, I need it to only retrieve the value of the corresponding month or quarter.

  1. I added rows to the excel spreadsheet to include the following line items for each entity and in both Actual and Budget:
  • Cash and Cash equivalent
  • Accounts receivable
  • Inventory
  • Other current assets
  • Accounts payable
  • Notes payable
  • Accrued expenses
  • Other payable

  1. Created a Balance Sheet category sheet that identifies sub-category: Assets and Liabilities, and Category: Current asset and Current liability.

  2. I refreshed the data into Power BI and created the following measures:

     BS.Actuals = 
 VAR CA = CALCULATE( SUM( 'Financial Data'[Value] ), 'Financial Data'[Actual/Budget] = "Actual",
                        FILTER( 'Balance Sheet Categories', 'Balance Sheet Categories'[Sub Category] = "Asset" ) )

 VAR CL = CALCULATE( SUM( 'Financial Data'[Value] ), 'Financial Data'[Actual/Budget] = "Actual",
                        FILTER( 'Balance Sheet Categories', 'Balance Sheet Categories'[Sub Category] = "Liability" ) )
 RETURN
 DIVIDE( CA + CL, 1000, 0 )

BS.Actuals-1M = CALCULATE([BS.Actuals], DATEADD(Dates[Date], -1, MONTH))

BS Actuals ('000s) = [BS.Actuals] - [BS.Actuals-1M]

The formulas works for the 1st Quarter but not for the 2nd, 3rd, and 4th.

Your input would be greatly appreciated.

Thank you
Selim

NB. How can I attach the Excel file?

image
image.PNG1960×996 104 KB

2

BS.Actuals-1M = CALCULATE([BS.Actuals], DATEADD(Dates[Date], -1, MONTH))

Are you sure the above formula is correct.

As your context is quarters but you compare months in your formula?

When you say the formula doesn’t work, what is wrong with it? There’s numbers showing so it’s hard for me to tell.

3

Sam,
I don’t know. I’m trying to create a logic that checks if each month corresponds to the date shown in my matrix, if so, return the value of that month only.

i.e, if the matrix uses Quarters as dates, then the formula should iterate between all dates and return the value of the last day of the months whose number corresponds to my Q number. (Q1 is month 3, then returned value is that of March 31).

Since BS.Actuals was returning the sum of values, I wanted to create a measure that would subtract the sum of proceeding months. AS.Actuals-1M seemed to just that.

In excel I have the following values for Cash and Cash Equivalent, Holston, Actual
1/31/2014: 434,903.22
2/28/2014: 434,599.71
3/31/2014: 456,407.81
4/30/2014: 522,967.38
5/31/2014: 548,943.70
6/30/2014: 538,019.72

The results I get for Q1:
BS.Actuals returns: 13,939 (sum of values for Jan, Feb & Mar)
BS.Actuals-1 returns: 12,531 (sum of Jan & Feb)
BS Actuals ('000s) returns: 456,407 (the correct number that corresponds to the value of March)

However when it comes to calculating Q2, my value returned of 81.61 is wrong as it should be June’s value of 538,019.

I hope the above makes sense .

4

This should be an easy fix.

You need to use this formula instead

BS.Actuals-1M = CALCULATE([BS.Actuals], DATEADD(Dates[Date], -1, **QUARTER))**

The reason it’s not working is because your only look at one month back rather than 3.

Understanding the ‘initial context’ is the key learning on this one.

I’ve gone an created the same formula you need and this is what it looks like

LQ Actuals ('000) = CALCULATE( [Actuals ('000)], DATEADD( Dates[Date], -1, QUARTER ) )