Forum Discussion

aamoody81's avatar
aamoody81
Regular Visitor
8 years ago
Solved

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 ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

    =IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Try this

 

=IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))

 

Hope this helps.

  • aamoody81's avatar
    aamoody81
    Regular Visitor
    8 years ago

    Thank you! This removed the line I wanted removed!

     

    =IF([Sum of ProductSales]=0,BLANK(),CALCULATE([Sum of ProductSales], SAMEPERIODLASTYEAR(D_DATE[DATE])))
    • AlbertoFerrari's avatar
      AlbertoFerrari
      Icon for Most Valuable Professional rankMost Valuable Professional
      8 years ago

      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