Budget to Actual data in two different Tables, sum on period

[BIhelpBtoA]

Hi Everyone!

I am quite new to Power BI and I was hoping for some assistance. I've read through some of the similar questions asked but I've had no luck trying to solve this, I'm sure it's partially to being new. I've got two tables that I'm trying to sum on fiscal period. One table contains budget data and the other contains our GL data which is the actual data. I'm trying to sum based on the fiscal period to simply show the amount that was budgeted for the period and the actual amount for the period. However, the budget amount is correct per period but the actual amount is showing the total of all periods in each individual period. I've tried to join these tables but i haven't been able to correctly so I'm trying to start over from scratch and hoping someone can help me or lead me in the right direction. Please let me know if I have included enough information for your assistance. The data tables are copied below but I can also share my PowerBI file if that is helpful. Thank you for any help that you can provide!

Data visualization with incorrect actual amount

Relationship view

Budget_2022

Fiscal Year	Fiscal Period	GL Account Code	GL Account Description	Department Code	Analytical Dimension Description 1	Product Code	Project Code	Budget
22	1	50005	Merchandise expense	400	Information Technology	000	000	-
22	12	50005	Merchandise expense	400	Information Technology	000	000	2,406.25
22	5	50005	Merchandise expense	400	Information Technology	000	000	6,000.00
22	7	50005	Merchandise expense	400	Information Technology	000	000	5,412.88
22	8	50005	Merchandise expense	400	Information Technology	000	000	13,900.00
22	12	50005	Merchandise expense	400	Information Technology	000	000	26,219.06
22	2	50010	Supplies Expense	405	G&A	000	000	-
22	12	50010	Supplies Expense	405	G&A	000	000	500.00
22	2	50010	Supplies Expense	405	G&A	001	001	20,600.00
22	1	50010	Supplies Expense	405	G&A	000	000	-

GL_Activity

Document Number	Document Description	Department Code	Analytical Dim 1 Desc.	Product Code	Project Code	GL Account Code	GL Account Description	Ledger Posting	Accounting Date	Fiscal Period
JE00005	Merchandise expense	400	Information Technology	000	000	50005	Merchandise expense	2799.24	2/28/2022	2
JE00005	Merchandise expense	400	Information Technology	000	000	50005	Merchandise expense	2799.29	2/28/2022	2
JE00005	Merchandise expense	400	Information Technology	000	000	50005	Merchandise expense	111.36	2/28/2022	2
JE00005	Merchandise expense	400	Information Technology	000	000	50005	Merchandise expense	111.35	2/28/2022	2
JE00005	Merchandise expense	400	Information Technology	000	000	50005	Merchandise expense	2799.23	2/28/2022	2
JE00006	Supplies Expense	405	G&A	001	002	50010	Supplies Expense	-12105	1/1/2022	1
JE00006	Supplies Expense	405	G&A	001	002	50010	Supplies Expense	12105	1/1/2022	1

[v-yanjiang-msft]

Hi @BIhelpBtoA ,

Does your GL_Activity table make relationship with the Calendar table by the Fiscal Period column? 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[DataInsights]

@BIhelpBtoA,

It appears that your visual is using Budget_2022[Fiscal Period]. The visual needs to use fields from the Calendar table.

[BIhelpBtoA]

Hi @DataInsights! 

I have pulled in the Order Date from the calendar table. In the viz "Year, quarter, month, date" are from the calendar table but those show as no values in them. 

Thanks for your response!

[DataInsights]

@BIhelpBtoA,

Could you share a link to your pbix file (OneDrive, etc.)? Be sure to remove sensitive data.

[BIhelpBtoA]

Hi @DataInsights! Very sorry about the delay. Got bogged down a bunch of different things. If you still have time to take a look here is the link to the file:  https://1drv.ms/u/s!AgUUbtkLlgi_aQMx51ImQwNwXxs

[DataInsights · Accepted solution]

@BIhelpBtoA,

Do the following:

1. Mark the Calendar table as the date table. Right-click the table and select "Mark as date table".

2. The relationship between Calendar and GL_Activity should use Calendar[Date] and GL_Activity[Accounting Date].

3. The relationship between Calendar and Budget_2022 needs a date column in Budget_2022. You can create a calculated column using the DATE function, and then create the relationship using this column.

4. In the visual, use Calendar fields.

[BIhelpBtoA]

That worked! I really appreciate the help!