Forum Discussion

Kbrennan856's avatar
Kbrennan856
Frequent Visitor
8 years ago

Dax Query

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])

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago
    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])