Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |