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.

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

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

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

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.