Building Variance Reports in Power BI

[MarcusClaudius]

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

[Greg_Deckler]

Probably, would want sample data and expected output. Your current report screen shots would help. See this Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

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

[MarcusClaudius]

Greg

I've just posted a few examples of the kinds of Excel reports that I produce.

Thanks for your help

[v-lili6-msft]

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

[MarcusClaudius]