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
- 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:
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” ) )
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.
NB. How can I attach the Excel file?