Sunday, 21 January 2018

How to Calculate YTD Sales for SAME PERIOD LAST YEAR using DAX Expression in Power BI

DAX Expression to Calculate YTD Sales for SAMEPERIODLASTYEAR in Power BI
Scenario:
Suppose We have the Sales Data for 2 consecutive Years 2015 and 2016. From this data the Customer interested to know YTD_Sales for Current Year as well as Same Period of the Last Year.
We can achieve this by the following way.
First We Create the Calculate a measure that Shows the YTD_Sales for the Current Year as follows :

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.

Now 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])
                                                   )
Now we can see the YTD_Sales and LY_YTD Sales calculated in below Cross Tab ( Matrix ) Reports.

YTD Sales for FY2015 :

YTD_Sales for FY2016 and LY_YTD_Sales(FY2015) :


Note :
Compare the above two Matrix reports two Check whether the LY_YTD Sales calculating correctly or not.

Next we can create a visualization with Trend Line Chart that shows the Sales Growth Momentum comparison by Month for the Fiscal Year 2015 and 2016.


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts