Consistency in Year-Over-Year Comparison Across Multiple Countries and Categories in DAX

[AkkiJadeja]

• DAX currently calculates growth compared to 2019 for four countries (Germany, Italy, France, Spain) across multiple categories (CF, AA, KT, ST).
• Italy only has data for the "CF" category, leading to blanks in weeks 1 to 6 in 2019.
• The same scenario might occur for other countries and categories, with missing data for certain weeks in 2019.
• To address this, we need to ensure that when comparing across multiple countries and categories, we consider only the weeks where the data available in both years.
• If any week is missing data for any country or category in 2019, the result for that week should be blank to maintain consistency in the comparison.
• This approach ensures a fair and accurate comparison of sales growth between this year and 2019, regardless of variations in data availability across countries and categories.
  
  Please check the below DAXs that we have at the moment, 
  
  
  Growth vs 2019 = var _year = [SelectedYear]

  var sellout_FY = CALCULATE ( [Sellout], dim_period[Year] = _year,  ALL(dim_period[Week date]),ALL(dim_product[Country]))

  var sellout_2019 = CALCULATE ( [Sellout],  dim_period[Year] = 2019,  ALL(dim_period[Week date]), ALL(dim_product[Country]))

  
  

  return

  IF(

      ISBLANK(sellout_FY) || ISBLANK(sellout_2019),

      BLANK(),

      DIVIDE(

          sellout_FY,

          sellout_2019

      ) - 1

  )
  ===============================================================
  

  Growth vs 2019 test =

  var _year = [SelectedYear]

  var flag_Italy =

      IF(

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Italy")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Italy")) > 0 &&

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = 2019 , FILTER(dim_product, dim_product[Country] = "Italy")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Italy")) > 0,

          1,

          BLANK()

      )

  
  

  var flag_Germany =

      IF(

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Germany")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Germany")) > 0 &&

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = 2019 , FILTER(dim_product, dim_product[Country] = "Germany")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Germany")) > 0,

          1,

          BLANK()

      )

  
  

  var flag_France =

      IF(

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "France")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "France")) > 0 &&

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = 2019 , FILTER(dim_product, dim_product[Country] = "France")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "France")) > 0,

          1,

          BLANK()

      )

  
  

  var flag_Spain =

      IF(

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Spain")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Spain")) > 0  &&

          NOT(ISBLANK(CALCULATE([Sellout], dim_period[Year] = 2019 , FILTER(dim_product, dim_product[Country] = "Spain")))) || CALCULATE([Sellout], dim_period[Year] = _year , FILTER(dim_product, dim_product[Country] = "Spain")) > 0,

          1,

          BLANK()

      )

  
  

  var total_sellout_2019 =

      CALCULATE(

          [Sellout],

          dim_period[Year] = 2019,

          ALL(dim_period[Week date]),

          (dim_product[Country] = "Italy" && flag_Italy = 1) ||

          (dim_product[Country] = "Germany" && flag_Germany = 1) ||

          (dim_product[Country] = "Spain" && flag_Spain = 1) ||

          (dim_product[Country] = "France" && flag_France = 1)

      )

  
  

  var total_sellout_2024 =

      CALCULATE(

          [Sellout],

          dim_period[Year] = _year,

          ALL(dim_period[Week date]),

          (dim_product[Country] = "Italy" && flag_Italy = 1) ||

          (dim_product[Country] = "Germany" && flag_Germany = 1) ||

          (dim_product[Country] = "Spain" && flag_Spain = 1) ||

          (dim_product[Country] = "France" && flag_France = 1)

      )

  
  

  return

  IF(

      ISBLANK(total_sellout_2019) || total_sellout_2019 = 0 || ISBLANK(total_sellout_2024) || total_sellout_2024 = 0,

      BLANK(),

      DIVIDE(

          total_sellout_2024,

          total_sellout_2019

      ) - 1

  )
  ==================================================================
  sellout = SUM(Sellout_Value)
  

  Please check the above image where we have the correct way of calulating the growth. here for ex: AA category having data in 2019 from W7 but 2024 have W1 to W16 so we want the comparision to be 
  "Sum of all countries sellout for category (AA) W7 to W16 from Italy, DE W1 to W17, W3 to W16 for ES data, W1 to W16 from FR" for 2019 
  Same for 2024 
  "Sum of all countries sellout for category (AA) W7 to W16 from Italy, DE W1 to W17, W3 to W16 for ES data, W1 to W16 from FR"

[v-kaiyue-msft]

Hi @AkkiJadeja ,

I can't help you very well. Maybe you could provide me with your pbix file?

When uploading pbix files, remove sensitive data and do not log into your account in Power BI Desktop.

Alternatively, you can create a virtual table to manually display your desired results and display them in screenshots or other forms.

All this is to better solve your problems.

I would be very grateful if you could get back to me as soon as possible.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.