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
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.
                                                    [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 open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts