Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SvG
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
kriscoupe
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

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
Anonymous
Not applicable

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

Best
D
kriscoupe
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors