Forum Discussion
Building Variance Reports in Power BI
To all
I'm trying to replicate the Variance Reports which I am currently producing in Excel.
The columns are your standard collections of Actuals v Budget, on month, and YTD. Followed by Forecast v Budget for the Full Year.
Whilst the rows are varied groupings (albeit solidly structured in a hierarchy) of cost centres/account codes interspered with subtotals.
There is also the fact that our Financial Year is not the same as the Calender Year (just to spice things up even further).
What I am struggling with is the sheer lack of versatility that Excel offers.
Of course Excel lacks the speed and dynamism that Power BI possesses.
So the simple question is. Is it possible to do make one?
Thanks
Mark Taylor
4 Replies
Probably, would want sample data and expected output. Your current report screen shots would help. See this Quick Measure:
For examples of calculating variances between years, etc.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Greg
I've just posted a few examples of the kinds of Excel reports that I produce.
Thanks for your help
hi,@MarcusClaudius
you can use TOTALYTD Function and TOTALMTD Function to create the measure like this:
SaleYTD = TOTALYTD(CALCULATE(SUM(Table1[sales])),'Date'[Date])
For example
and use SAMEPERIODLASTYEAR Function create the measure
Previous sales=CALCULATE(SUM(Table1[sales]), SAMEPERIODLASTYEAR('Date'[Date]))Then use these measures and other fields to create your report.
Best Regards,
Lin
