Comparing Actual vs Budget vs Last Year Results in Power BI w/DAX

Use this great technique to visualise how you are performing versus budgets and last years results. View the information cumulatively for added impact. Plenty of ideas to work through during this tutorial including; cumulative totals, time intelligence, using variable, commenting in DAX code and much more.

***** Video Details *****
2:25 - total sales
2:46 - budget allocation per day
3:56 - total sales last year
4:30 - cumulative sales
5:31 - cumulative sales last year
5:43 - cumulative budgets
5:58 - visualisation

***** Learning Power BI? *****

***** Related Links *****

***** Related Course Modules *****

***** Related Support Forum Posts *****

[EnterpriseDNA]

Learning Power BI?

Related Links

Related Course Modules

Related Support Forum Posts

[alejorivas]

Awesome video! Have a question, I'd like to do something similar, but I have a monthly budget. How can I convert every month's budget to a daily budget? Cheers

[covid]

I have 3 tables: actuals, budgets and date. I have a matrix table showing actuals vs budget by region but my budget totals show cumulative rather than a breakdown by region. Am I missing a relationship between the Actuals and Budget table?

[jimmyni]

Excellent! Lots of usage with this pattern. Thank you so much. Big hug to you.

[aatsw]

Your Power BI videos are very informative. Thank you for your great work.

[russmatthews]

One issue that might effect many is the working days aspect.  My business is closed on weekends and holidays so dividing the budget by 365 days is not correct.  Regardless, you are amazing and this video is amazing.  Thank you for all you do.

[kiterbram]

Great video, Sam!

I have a question: How could you calculate the last years (full fiscal year, so 12 months) and visualize it accumulating?
So you could compare and visualize current vs previous fiscal year.

The only page filter I use at the moment which is important to consider is a . My FY runs July-June.
Besides that, I am using same (date) table techniques as your video.

I tried below two formulas but that didn't seem to work out.

1) PY = CALCULATE([Total cost], DATESYTD( SAMEPERIODLASTYEAR(dim_Dates[Date]), "30/06"))

If I add a table with fiscal periods, it returns accumulated values for each period, instead of values for each fiscal period (although the total is showing correct)

2) My below PYTD measure does show correct values for each period, but the problem there is that it only shows values until the month that we are in.
E.g. if I have data up to January 2022, it shows month by month values but only from July 2020 up to January 2021.

I would like to have it show month by month values up to the end of the previous fiscal year. I guess that error has to do with the YTD measure.

PYTD = CALCULATE([YTD], DATEADD(dim_Dates[Date], -1, YEAR))

YTD =
VAR Maxmonth = Period]), ALL(dim_Dates), = 0 )
RETURN CALCULATE( [Total cost], dim_Dates[Fiscal Period] <= Maxmonth )

I hope you could help me out!


Thanks

[NirRobinson]

Thank for the clear explanation !!
Love your work.

[DanielWeikert]

Great Video. I have a question you might help with. Sometimes I am not sure whether to refer to a column or a table in a formula. E.g. when calculating a running total I used ALL as well as ALLSELECTED with the whole Calendartable and with the Dates Column from the Calendartable. Both work but I am not totally sure that this is always the case? Any help, videos or other resource file for reference? Thanks

[ThePunisher]

Thanks for sharing. I'm following the same however I'm getting the data starting in different locations.
I'm in 2021, so last year is complete.
My forecast is 2023.
I need to measure this year vs last year vs forecast.
How to let all data start from 0?

[Graylinepartners]

I was not able to get the same visualization as your example. It may have to do with my Date that I used for the x axis.
I used this: Dates = calendar(date(2017, 1, 1), date(2021, 12, 31)) for my dates calendar and then used Dates[Date] as my x axis.

[garyallan]

Thank you so much for your great video. My only issue is with dates, I am from Australia which uses a July to June financial year. All the date functions work on Jan to December which doesn't work. (Also with the sorting / Listing of the months). Is there anyway to fix this?

[ricardoalves]

Hi, I´m trying to do cumulative budget based on a yearly budget, but in my data i have months prior to budget (for example budget 2018, but 2017 is filtered), and cumulative in first month is already 70% of budget...Any ideia how to solve this? Thanks!

[luludezheng]

can you show me how your Dates table is structured?