SAMEPERIODLASTYEAR not all category values are showing

[SvG]

Hi,

I've been searching but can't seem to find the right answer, so looking for your help.

I'm using the following formula:

SalesLastYear = CALCULATE( SUM(Sales_DailyReport[Net amount]),SAMEPERIODLASTYEAR(Sales_DailyReport[Invoice Date]))

However, I'm working with different product caterogies. Now, if a product category hasn't been sold on a particalur day, the formula won't retrieve the value for that product catergory for the same day last year either.

For example:

Sold on 6 May 2020:	Sold on 6 May 2019:	My (incorrect) result of formula, sold on 6 May 2019:
Product 1: $700	Product 1: $500	Product 1: $500
Product 2: $200	Product 3: $50

So I need to see the total of $550, but the formula doesn't 'see' product 3 as it was not sold on 6 May 2020.

I have tried multiple variations of DAX, including ALL , VALUES (to include the product categories) and DATEADD, .[date], PARALLELPERIOD (to try different ways), all without success.

Any thoughts please?

Thanks!

[kriscoupe · Accepted solution]

Hi @SvG,

I think the issue here comes down to the DAX auto-exists concept. You should build a proper date table to do your time-intelligence on. You see, the auto-exist concept allows DAX to be efficient and since product 3 doesn't exist in the original context my take on it is that it is "lost" due to the auto-exist optimisation of the DAX language, even when SAMEPERIODLASTYEAR shifts the context.

Try adding a date table and using SAMEPERIODLASTYEAR on this instead. Also refer to this article.

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Hope it helps

Kris

[Anonymous · Accepted solution]

Time-intel functions work correctly ONLY on proper date tables. Just create a correct star-schema model.

Best
D

[kriscoupe · Accepted solution]

Hi @SvG,

I think the issue here comes down to the DAX auto-exists concept. You should build a proper date table to do your time-intelligence on. You see, the auto-exist concept allows DAX to be efficient and since product 3 doesn't exist in the original context my take on it is that it is "lost" due to the auto-exist optimisation of the DAX language, even when SAMEPERIODLASTYEAR shifts the context.

Try adding a date table and using SAMEPERIODLASTYEAR on this instead. Also refer to this article.

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Hope it helps

Kris