cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

SAMEPERIODLASTYEAR not all category values are showing

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: $700Product 1: $500Product 1: $500
Product 2: $200Product 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!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: SAMEPERIODLASTYEAR not all category values are showing

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

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: SAMEPERIODLASTYEAR not all category values are showing

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

Best
D

View solution in original post

2 REPLIES 2
Highlighted
Resolver IV
Resolver IV

Re: SAMEPERIODLASTYEAR not all category values are showing

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

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: SAMEPERIODLASTYEAR not all category values are showing

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

Best
D

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors