Scenario:
The relationships between tables are as follows:
The sample data for the Current Year (2023) and Previous Year (2022) is as per below:
In the below sample data, for Current Year (2023), the data is only available for until 03rd May of W1.
For the Previous Year (2022) we have data for the Whole Year, until the last Week of Year.
In this Case, if we want to calculate the Sales for same Period Last Year, we need to consider only the same dates ( From 01Jan2022 to 03May2022).
Units Sold = SUM(tbl_Sales[Units_Sold])
Now we will see how we can calculate the Sales (Units Sold) for the same period last using various Methods.
1) Using SAMEPERIODLASTYEAR Method:
The following is a very standard method that considers all the Dates in Previous Year instead of exact dates. Hence this method will NOT return the accurate results.
Units Sold LY =
CALCULATE( [Units Sold],
SAMEPERIODLASTYEAR(DimCalendar[DimDate]))
)
YoY Growth =
VAR _CY = [Units Sold]
VAR _LY = [Units Sold LY]
VAR _Ratio = DIVIDE(_CY,_LY)-1
RETURN
IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())
2) Using DATESBETWEEN Method:
The following method considers all Dates in Previous Year until end of the same Month (May) based on the Max Sales Month in Current Year.
This method also will NOT return the accurate results, as the Current Month (May) is not yet completed in the Current Year.
Units Sold LY (till Same Month) =
VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)
VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12)
VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])
VAR _LY_Dates = DATESBETWEEN(DimCalendar[DimDate], _MinDtLY, _LimitMaxDtLY)
VAR _LY_Sales = CALCULATE( [Units Sold], _LY_Dates)
VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())
RETURN
_Result
YoY Growth (till Same Month) =
VAR _CY = [Units Sold]
VAR _LY = [Units Sold LY (till Same Month)]
VAR _Ratio = DIVIDE(_CY,_LY)-1
RETURN
IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())
The results of the above 2 Methods are as follows:
—————————————————————-
3) Using SAMEPERIODLASTYEAR Method (same Dates from LY):
This method considers only exact same dates from the Last Year to calculate Sales for same period Last Year.
Units Sold LY (till Same Dates Method1) =
VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)
/* VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12) */
VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])
VAR _LimitDatesLY =
SAMEPERIODLASTYEAR(
FILTER(VALUES(DimCalendar[DimDate]),
DimCalendar[DimDate]<= _MaxSaleDT
)
)
VAR _LY_Sales = CALCULATE( [Units Sold], _LimitDatesLY)
VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())
RETURN
_Result
YoY Growth (Same Dates Method1) =
VAR _CY = [Units Sold]
VAR _LY = [Units Sold LY (till Same Dates Method1)]
VAR _Ratio = DIVIDE(_CY,_LY)-1
RETURN
IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())
4) Using DATESBETWEEN Method (same Dates from LY):
This method considers only exact same dates from the Last Year to calculate Sales for same period Last Year.
Units Sold LY (till Same Dates Method2)=
VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)
/* VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12) */
VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])
VAR _LimitMaxDtLY =
LASTDATE(
SAMEPERIODLASTYEAR(
FILTER(VALUES(DimCalendar[DimDate]),
DimCalendar[DimDate]<= _MaxSaleDT
)
)
)
VAR _LY_Dates = DATESBETWEEN(DimCalendar[DimDate], _MinDtLY, _LimitMaxDtLY)
VAR _LY_Sales = CALCULATE( [Units Sold], _LY_Dates)
VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())
RETURN
_Result
YoY Growth (Same Dates Method2) =
VAR _CY = [Units Sold]
VAR _LY = [Units Sold LY (till Same Dates Method2)]
VAR _Ratio = DIVIDE(_CY,_LY)-1
RETURN
IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())
The results of the above 2 Methods are as follows:
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment