Anual Budget Dataset

[colin_dickson]

Hi,

I'm looking to create a static annual budget and use it for comparison purposes.

So my data is committment accounting and therefore is always a full year forecast split across multiple teams and months/weeks. I have an annual budget split by team, which does not change throughout the year.

What i'd like to do is have that static budget column each time when analysing the team data regardless of the month and then be able to do one minus the other.. a bit like the below:

Team	Forecast Cost	Budget	Variance
1	400	300	(100)
2	450	400	50
3	500	300	(200)
4	300	400	100

I've no doubt this is possible but anyones help woule be much apprecaited.

Thanks

Colin

[Anonymous]

Hi  @colin_dickson ,

You can create a separate table to act as a static budget

Here are the steps you can follow：

1. Enter data.

2. Create calculated column.

Variance =
ABS(
[Forecast Cost]
-
SUMX(
    FILTER(ALL(Table2),
    'Table'[Team] in SELECTCOLUMNS('Table2',"1",'Table2'[Team])&&
    'Table2'[Team]=EARLIER('Table'[Team])),
    'Table2'[Budget])
)

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@colin_dickson , For static budget you can create a measure like

Budget = 1000

of use numeric parameter https://docs.microsoft.com/en-us/power-bi/desktop-what-if