Forum Discussion
SAMEPERIODLASTYEAR for only values in current year
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
Hi,
Try this
=IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))
Hope this helps.
7 Replies
- AlbertoFerrari
Most Valuable Professional
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.comI 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.
- Greg_Deckler
Community Champion
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"
- Ashish_Mathur
Super User
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])))
- AlbertoFerrari
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
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.
