Forum Discussion
Problem when presenting sameperiodlastyear data based on weeknumbers
I have a problem in DAX using the SAMEPERIODLASTYEAR function when presenting the data based on weeknumbers. I am struggling with this for more than 10 hours and still did not found the cause of the problem..:(
I am having a cumulative sales measure (year to date):
YTDSales = CALCULATE(SUM(Sales[SalesAmount]);DATESBETWEEN(Date[Date];DATE(YEAR(MAX(Date[Date]));1;1);MAX(Date[Date])))
The measure for creating the 'Sales Previous Year':
SalesPreviousYear = CALCULATE([YTDSales];SAMEPERIODLASTYEAR(Date[Date]))
My date table is dynamic, based on SalesDates (minSalesDate and maxSalesDates are variables):
CALENDAR(DATE(YEAR(minSalesDate);MONTH(minSalesDate);DAY(minSalesDate));DATE(YEAR(maxSalesDate);MONTH(maxSalesDate);DAY(maxSalesDate)))
Everything works fine when I present the data within a table using the columns Date, YTDSales, SalesPreviousYear. When I replace the date column for weeknumber("ww") and set a report filter on year 2017, it does not show the right value for the previous year. The date table is generated until 01-06-2017 and the incorrect value is for weeknumber 22. I think it show a value that should be presented around week 25/26/27.
Does anyone know the cause of the problem that it does not show the correct data for the previous year when I present the data per weeknumber?
Thank you very much.
The inbuilt time intelligence functions must use the date column. If you want to use anything else (like a week number) you must use custom time intelligence. I explain that here http://exceleratorbi.com.au/dax-time-intelligence-beginners/
4 Replies
- MattAllingtonCommunity Champion
Is there any reason why you can't use totalytd() to calculate your YTD number? That is the first thing I would try
- AnonymousNot applicable
Thanks for your reply MattAllington
It is a good point, I will change it. But it does not solve my problem:(
- MattAllingtonCommunity Champion
The inbuilt time intelligence functions must use the date column. If you want to use anything else (like a week number) you must use custom time intelligence. I explain that here http://exceleratorbi.com.au/dax-time-intelligence-beginners/