Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
colin_dickson
Frequent Visitor

Anual Budget Dataset

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:

 

TeamForecast CostBudgetVariance
1400300(100)
245040050
3500300(200)
4300400100

 

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

 

Thanks

 

Colin

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1680595091273.png

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:

vyangliumsft_1-1680595091275.png

 

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
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.