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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Resolver V
Resolver V

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
Resolver V
Resolver V

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors