PowerPivot Nuggets - Part 16 - Berekening Voordeel Alle Aard (VAA) Bedrijfswagens

In Belgium there is a lot of buzz going around the higher taxes on company cars.  In Dutch it's called "Voordeel Alle Aard".  The goverment changed the way this tax is calculated.  Everybody with a company car in Belgium is now trying to find out wether he will pay more or less taxes.  Although we know the answer in advance :-)

Time to put this into a PowerPivot workbook.  The workbook uses the concepts I described in my previous blogpost PowerPivot Nuggets - Part 15 - What If Analysis with Slicers.

The tax used to be calculated on two parameters: CO2 emissions of your car and the distance between your house and your workplace.

In DAX the formula looks like this:

IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)

;VALUES(CO2[CO2 Uitstoot])*0,00237 * VALUES(ForfaitAfstand[ForfaitAfstand 2011])
;BLANK())

The new formula is more complex.  CO2 emissions, age and the list price of the car play a role. In DAX it looks something likes this:

IF(HASONEVALUE(CO2) && HASONEVALUE(CatalogusPrijs) && HASONEVALUE(ForfaitAfstand) &&HASONEVALUE(Leeftijd)
;

   (( VALUES(CatalogusPrijs[CatalogusPrijs])

   * (((( VALUES(CO2[CO2 Uitstoot])-95)*0,1)+5,5)/100))*0,857143)

   * VALUES(Leeftijd[Pct])
;BLANK())

Since the "Voordeel Alle Aard" is minimum 1200 EUR/year I need to write a wrapper around this calculation.

 Jaarlijkse Bijdrage Nieuw:=IF(Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden] < 1200;1200; Bijdrage[Jaarlijkse Bijdrage Nieuw Hidden])

Final step is to create a workbook with 3 slicers (Home-Work Distance, List-Price of the Car and CO2 Emission).  By choosing the right values for your car the workbook will calculate the old and new "Voordeel Alle Aard" and the difference between the two.

You can download the workbook here. You need at least Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010 RC0.

THIS WORKBOOK IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL FOR POWERPIVOT DEMO PURPOSE, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED.