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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Supplier
Solution Supplier

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
Solution Supplier
Solution Supplier

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.