Actual Vs Budget table in PowerBi

[JeanC]

Hello All,

Can anyone help me to setting up Actual Vs Budget table in PowerBi. Is there a way I can attached datasets?

Thanks in Advance
Jean

[JeanC]

I would like to have an Actual Vs Budget table that show monthly variances, quarterly variances as well year-to-date variances.

[v-jianpeng-msft]

Hi, @JeanC 

I calculate the difference for each month using the following DAX:

this month = 
var _cur_month = CALCULATE(
    SELECTEDVALUE('DateTable'[year number]),'DateTable'[Date]=TODAY())
RETURN
CALCULATE(
    [Project Amount],
    'DateTable'[year number]=_cur_month)


Last Months = 
var _cur_month = CALCULATE(
    SELECTEDVALUE(DateTable[year number]),'DateTable'[Date]=TODAY())
RETURN CALCULATE([Project Amount],'DateTable'[year number]=_cur_month-1)


month-on-month = DIVIDE([this month]-[Last Months],[Last Months])

The calculation results are as follows:

I have provided the PBIX file used this time. If it can help you, that would be great.

How to Get Your Question Answered Quickly

Best Regards

Jianpeng Li

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

[JeanC]

 if someone can help create and save the .pbix file I would appreciate it.

[JeanC]

Above are the tables/datasets

[JeanC]

Dept Head	OU	Asset Category	Department Number	Department Name	Department Description
Doe John	745	FACil	111	Facilities	Doe John-111-Facilities
Smith John	746	IT	112	IT Services	Smith John-112-IT Services
Wilson Mike	747	Med&Veh	113	Med&Veh-Miami	Wilson Mike-113-Med&Veh-Miami

[JeanC]

Date	Year	Period	Month
Jan 2024	2024	1	Jan
Feb 2024	2024	2	Feb
Mar 2024	2024	3	Mar

[JeanC]

Scenario	Scenario ID	Scenario Description
Actual	1	Actual
Budget	2	Budget
Estimate	3	Estimate
Forecast	4	Forecast

[JeanC]

Account	Long Account Description	Asset Category	Asset SubCategory	Asset Category ID	Asset Category Code
1560	1560-Computer Hardware	IT	Computer Hardware	60	1560-IT
1540	1540-Leasehold Improvements	facil	Leasehold Improvements	40	1540-facil
1580	1580-HME Property and Equipment	Med&Veh	HME Property and Equipment	80	1580-Med&Veh

[JeanC]

Date	Amount	Account	Project Code	Project Description	OU	OU Description	Asset Category	Dept Head	Scenario
Jan 2024	$           50,000.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Budget
Jan 2024	$        166,666.67	1540	4896	Facil Project description 1	894	Oakland	facil	Doe John	Budget
Jan 2024	$           38,500.00	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Budget
Feb 2024	$           50,500.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Budget
Feb 2024	$        167,166.67	1540	4896	Facil Project description 1	894	Oakland	facil	Doe John	Budget
Feb 2024	$           39,000.00	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Budget
Mar 2024	$           51,000.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Budget
Mar 2024	$        167,666.67	1540	4896	Facil Project description 1	894	Oakland	facil	Doe John	Budget
Mar 2024	$           39,500.00	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Budget

[JeanC]

Date	Amount	Account	Project Code	Project Description	OU	OU Description	Asset Category	Dept Head	Scenario
Jan 2024	2,500.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Actual
Jan 2024	12,011.39	1540	4896	Facil Project description 1	894	Oakland	Facil	Doe John	Actual
Jan 2024	64,639.83	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Actual
Feb 2024	3,000.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Actual
Feb 2024	12,511.39	1540	4896	Facil Project description 1	894	Oakland	Facil	Doe John	Actual
Feb 2024	65,139.83	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Actual
Mar 2024	3,500.00	1560	3022	IT Project description 1	107	Omaha	IT	Smith John	Actual
Mar 2024	13,011.39	1540	4896	Facil Project description 1	894	Oakland	Facil	Doe John	Actual
Mar 2024	65,639.83	1580	6418	Med&Veh Project description 1	917	Med&Veh - Irving	Med&Veh	Wilson Mike	Actual

[JeanC]

Here's table sample above