Dax Query

[Kbrennan856]

Hi,

I am comapring last year products with this years products. I have used the below dax code, i filter on the year as we use an accounting week. My problem is we have some old products in 2016 that we do not sell in 2017, The Percentage then is 100%. what should i do to only compare products sold in both this year and last.

Year on Year = if(CALCULATE([PPK],Query1[Year]=2016),[PPK]-CALCULATE([PPK],Query1[Year]=2017),[PPK])

[Eric_Zhang]

---

@Kbrennan856 wrote:

Hi,

I am comapring last year products with this years products. I have used the below dax code, i filter on the year as we use an accounting week. My problem is we have some old products in 2016 that we do not sell in 2017, The Percentage then is 100%. what should i do to only compare products sold in both this year and last.

Year on Year = if(CALCULATE([PPK],Query1[Year]=2016),[PPK]-CALCULATE([PPK],Query1[Year]=2017),[PPK])

---

@Kbrennan856

How you calculate the percentage? Maybe you can use IF statement, when the last year sales is blank return blank instead.

IF(ISBLANK([lastyearsales]),BLANK(),[yourPrecDax])

[Greg_Deckler]

Perhaps have a product table where you do a COUNT for 2017 and 2016 and if the COUNT for 2016 comes back 0, flag that as FALSE, otherwise TRUE. Then you could filter your measure to just TRUE values.