Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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.

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion

    Is there any reason why you can't use totalytd() to calculate your YTD number?  That is the first thing I would try