Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to show the value from prior year for products. I only want the products that are present in the current year to show.
What I would like:
Product SamePeriodSalesLastYear 1 $10 2 $12
What I am getting:
Product SamePeriodSalesLastYear
1 $10
2 $12
3 $8 --Product 3 has no sales in the current year
CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE]))
OR
CALCULATE([Sum of ProductSales]), PARALLELPERIOD(D_DATE[DATE],-12,month))
Any help on this would be really appreciated.
Thank you
Solved! Go to Solution.
Hi,
Try this
=IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))
Hope this helps.
Dear friends, unfortunately this function has no solution if there is an error. It is the worst function of Power BI, because it is necessary but only works in an unknown condition and does not work in others. It's a pity that this happens in an excellent solution like Power BI. There will be no solution. If it works, use it if you do not use any other function. That's the truth.
Hi,
Try this
=IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))
Hope this helps.
Thank you! This removed the line I wanted removed!
=IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))
Hi Aamoody,
If the formula works for you, then it's fine. Nevertheless, keep in mind that the code with IF is not computing what you asked at the beginning of the thread.
If you slice by product, then it is ok, every row will show sales in the previous year if there are sales in the current one. But on subtotals and at any grain over the product, the result might not be what you need.
If Products A and B belong to category Veggies, the code you are using will show the total of A and B in the last year, even if in the current one you sold only A.
The one with VALUES and CALCULATE shows only sales of A in the previous year, removing B because B has not been sold in the current year.
Might look like a pedantic difference, but it might be an important one.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
This (or something similar, of course) should work:
= CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( Date[Date] ), VALUES ( Sales[ProductKey] ) )
Basically, you move hte filter on the date, but you consolidate the filter over the products based on the current product keys.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
I think this was really close.
= CALCULATE ( SUM ( Sales[Amount] ), SAMEPERIODLASTYEAR ( Date[Date] ),
Sales[ProductKey] IN VALUES ( Sales[ProductKey] ) )
I found this below. Unfortunatley for me the IN function isnt avaiable in my version.
https://stackoverflow.com/questions/48301281/calculate-previous-year-based-on-this-year-condition
Thank you for your respose.
Can't you just wrap that in an IF statement such that "if the sum of the sales in the current year is 0, then blank, otherwise, the calculation you mention"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |