How to Calculate the Percentage of Sales Variance using DAX Expressions in Power BI

DAX Expressions to Calculate the Percentage of Sales Variance in Power BI
Scenario:
Suppose We have the Sales Data for 3 consecutive Years 2014, 2015 and 2016. From this data the Customer is interested to know YTD Sales Variance between the Years FY2015 Vs. FY2014 and FY2016 Vs. FY2015 Data. We can achieve this by the following way.

First We Create the Calculate a measure that Shows the YTD_Sales:

YTD_Sales = TOTALYTD(SUM(FactSales[Gross_Sales]),Tbl_Calendar[Date_Id])

Here, Tbl_Calendar is a related Table whose [Date_Id] has a relation with [Order_Date] of the the [FactSales] Table.

Next , based on this Measure, we can calculate the YTD Sales for the SAME PERIOD LAST YEAR using the following DAX Expression.

LY_YTD_Sales = CALCULATE(
                                                    [YTD_Sales],                                                                                                                              SAMEPERIODLASTYEAR(Tbl_Calendar[Date_Id])
                                                   )

Next, based on the YTD_Sales , LY_YTD_Sales Measures we can calculate the YTD Sales Variance using the below DAX Expression:

YTD_Sales_Var = [YTD_Sales]-[LY_YTD_Sales]

Finally, we can calculate the Percentage of YTD Sales Variance using the below DAX Expression:

YTD_Sales_Var% = DIVIDE([YTD_Sales_Var],[LY_YTD_Sales])

Now we can see the YTD_Sales , LY_YTD Sales, YTD_Sales_Var, YTD_Sales_Var%  calculated in below Cross Tab ( Matrix ) Reports :

FY2015 vs FY2014 Data :

 FY2016 vs FY2015 Data :

YTD Sales Variances ( vs. Previous Years) :

Next we can create a visualization with Waterfall Chart that shows the YTD_Sales_Var%      for the Fiscal Years 2015(vs.2014) and 2016(vs.2015). 

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------