Forum Discussion
Last Year Sales Giving In-correct output
I have a date table created with the formula `Date = CALENDAR(MIN('RetailTransactions'[BusinessDate]), TODAY())`, which is connected to my sales table through a one-to-many relationship. When I calculate last year's sales using the following formula:
```DAX
Total Sales Line Last Year =
ABS(CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
SAMEPERIODLASTYEAR('Date'[Date])
))
```
It works correctly when I select a date range from 01-09-24 to 27-09-24, showing accurate figures. However, if I select the range from 01-09-24 to 28-09-24, the calculation for last year shows the entire month rather than limiting it to the same range until the 28th of last year.
This issue also occurs with my last year Month-to-Date (MTD) and Year-to-Date (YTD) calculations.
Instead of using SAMEPERIODLASTYEAR, which returns the entire month when a larger date range is selected, you can use the DATEADD function to get last year's sales while maintaining the context of the selected dates.
Total Sales Line Last Year =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATEADD('Date'[Date], -1, YEAR)
)
)Last Year Month-to-Date (MTD)
LY MTD Sales =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATESMTD(DATEADD('Date'[Date], -1, YEAR))
)
)Last Year Year-to-Date (YTD)
LY YTD Sales =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATESYTD(DATEADD('Date'[Date], -1, YEAR))
)
)Your Kudos/Likes are much appreciated!
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Regards,
Kedar Pande
www.linkedin.com/in/kedar-pandeThank you DataNinja777 , dharmendars007 and Kedar_Pande ,Your insights are very good. Here are some additional points.
Hi, Thalbish
I think you can refer to the daxpatterns article related to dates, which describes in detail how to use the date table to calculate MTD, YTD, PMTD, PYTD:
Week-related calculations – DAX Patterns
MTD:
YTD:
PYTD:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Thalbish ,
The issue arises because the SAMEPERIODLASTYEAR function is designed to return the same period in the previous year based on the full date range selected in the current year. This means that if the selected date range in the current year (e.g., 01-09-24 to 28-09-24) doesn't have matching dates for the last year (due to missing data or future dates), it might return an unexpected range.
To resolve this and limit your last year calculations to only valid dates within last year’s range, you can introduce logic that ensures the selected dates don't go beyond the available data for the previous year.
Here’s how you can adjust your measure:
Total Sales Line Last Year = VAR LastYearMaxDate = CALCULATE( MAX('RetailTransactions'[BusinessDate]), SAMEPERIODLASTYEAR('Date'[Date]) ) VAR FilteredDateRange = FILTER( SAMEPERIODLASTYEAR('Date'[Date]), 'Date'[Date] <= LastYearMaxDate ) RETURN ABS(CALCULATE( SUM(RetailTransactionSalesLines[NetAmount]), FilteredDateRange ))Best regards,
Hello Thalbish
Please give a try on the below measure...
Total Sales Line Last Year =
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
FILTER(
ALL('Date'),
'Date'[Date] >= DATEADD(MIN('Date'[Date]), -1, YEAR) &&
'Date'[Date] <= DATEADD(MAX('Date'[Date]), -1, YEAR)))If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
Its giving error,
The first argument to 'DATEADD' must specify a column.
Instead of using SAMEPERIODLASTYEAR, which returns the entire month when a larger date range is selected, you can use the DATEADD function to get last year's sales while maintaining the context of the selected dates.
Total Sales Line Last Year =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATEADD('Date'[Date], -1, YEAR)
)
)Last Year Month-to-Date (MTD)
LY MTD Sales =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATESMTD(DATEADD('Date'[Date], -1, YEAR))
)
)Last Year Year-to-Date (YTD)
LY YTD Sales =
ABS(
CALCULATE(
SUM(RetailTransactionSalesLines[NetAmount]),
DATESYTD(DATEADD('Date'[Date], -1, YEAR))
)
)Your Kudos/Likes are much appreciated!
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Regards,
Kedar Pande
www.linkedin.com/in/kedar-pandeThank you DataNinja777 , dharmendars007 and Kedar_Pande ,Your insights are very good. Here are some additional points.
Hi, Thalbish
I think you can refer to the daxpatterns article related to dates, which describes in detail how to use the date table to calculate MTD, YTD, PMTD, PYTD:
Week-related calculations – DAX Patterns
MTD:
YTD:
PYTD:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
