P&L by month combining actuals and budget

[brobertson017]

Hello,

I have a question similar to this one, but not quite the same and I haven't been able to modify the infomarion there to solve my problem.

I'm trying to create a Profit & Loss table showing accounts on the rows and months on the headers. I'd like to take a user-input month and show actual results up to that month and budget results for remaining months in the year. So if the user selects February, then the Jan and Feb columns will show actual results and Mar to Dec columns will show budget results.

My data is pulled from Netsuite. Dates are converted to Accounting Periods so January 2019 would be accounting period 149, Feb/2019 is 150, etc. My actual and budget amounts are each in their own Fact Table with columns for Accounting Period, Account Number and Amount. I have created measures to calculate the actual and budget amounts, and I can easily make a table showing both budget and actual results for each month, but I can't put it all together.

Hopefully I have provided enough information. Any help is appreciated. Thank you.

[TeigeGao]

Hi @brobertson017 ,

Could you please share some sample data and expected result to us for analysis?

Best Regards,

Tiege

[brobertson017]

I'm not able to upload my model since it has work data in it, but maybe I could explain it?

The first query is the actuals data, in a fact table whose final output looks like this:

ACCOUNTING_PERIOD_ID	ACCOUNT_ID	ACTIVITY_DATE	DEPARTMENT_ID	SUBSIDIARY_ID	AMOUNT
85	148	1/19/2017	16	8	23.51
85	148	1/19/2017	17	8	494.4
85	148	1/19/2017	21	5	1829.44
85	148	1/19/2017	11	5	14.02

The second query is budget data, in a fact table with similar output except no column for activity date:

ACCOUNTING_PERIOD_ID	ACCOUNT_ID	AMOUNT	DEPARTMENT_ID	SUBSIDIARY_ID
85	465	300	9	8
98	465	300	9	8
99	465	300	9	8
101	465	300	9	8

I then have a calendar table as well as several DIM tables to convert the number values back into actual department, subsidiary, etc., names.

My relevant Measures are:

Actuals:=SUM(FACT_POSTING_ACCOUNT_ACTIVITY[AMOUNT])

Budget:=SUM(FACT_BUDGET[AMOUNT])

Then I make a pivot table with my Accounts on the rows and Dates on the columns (in this case 12 columns - Jan to Dec, 2019). If I pull in the Actuals measure in the Values field it shows the actuals (of course!) and if I pull in the Budget measure it shows budget values (so far so good). But if I pull them both in, I get something like this:

	January		February		March		April		May		June		July		August		September		October		November		December
Account	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget	Actuals	Budget
Revenue 1	(369,218)	(109,821)	(322,458)	(37,407)	(306,474)	(259,384)	-	(53,083)	-	(285,989)	-	(413,648)	-	(396,097)	-	(451,782)	-	(244,600)	-	(16,835)	-	(249,925)	-	(226,430)
Revenue 2	-	-	-	(380,820)	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Revenue 3	-	(455,387)	-	(150,773)	-	(425,808)	-	(595,803)	-	-	-	-	-	(131,980)	-	-	-	-	-	(320,111)	-	-	-	-

My goal is to be able to select a month (March for example) and have actuals show for Jan to Mar and then Budget to show for Apr to Dec, like this:

	January	February	March	April	May	June	July	August	September	October	November	December
Account	Actuals	Actuals	Actuals	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget	Budget
Revenue 1	(369,218)	(322,458)	(306,474)	(53,083)	(285,989)	(413,648)	(396,097)	(451,782)	(244,600)	(16,835)	(249,925)	(226,430)
Revenue 2	-	-	-	-	-	-	-	-	-	-	-	-
Revenue 3	-	-	-	(595,803)	-	-	(131,980)	-	-	(320,111)	-	-

In my head it's a simple if/then check. If accounting_period_id is less than or equal to the accounting_period_id of the month I select, return actuals, if not, return budget. The problem I ran into when trying to turn this into proper syntax is that the selected month (and equivalent accounting_period_id) has to be compared to two different fact tables, and then return some information from one fact table and some from the other.

Does that help explain? If not I can try to scrub sensitive data from the model and upload it.

Thank you.

[brobertson017]

Am I allowed by bump my thread, @TeigeGao ? I hope so! Thanks.