Forum Discussion

Thomas_Ramhapp's avatar
Thomas_Ramhapp
Regular Visitor
5 years ago
Solved

YoY Qty Measure

Hi everyone, Power BI newbie here, i created a visual with Partnumbers - Qty 2020, Qty 2019 - Total Qty (2020+2019). Now i just want to calculate the differnce of this both years. Not in YoY% but i...
Need Help
  • Thomas_Ramhapp's avatar
    Thomas_Ramhapp
    5 years ago

    tenfingers Anonymous amitchandak 

    Thanks Guys for your help. I found a very simple soloution from my problem.

    i mades some new basic measures.

     

    1. i summarized all the sales =SUM(TotalSales[Qty])

    2. Qty Last Year = CALCULATE(TotalSales[Qty], SAMEPERIODLASTYEAR (Date[Date]))

    3. Differnece from This year to Last year = IF( ISBLANK ([QtyLY]) , BLANK(), [TotalSales]-[QtyLY] )

     

     

    result fits perfect for me.

     

    Thx again

    regards Tom

     

Thomas_Ramhapp's avatar
Thomas_Ramhapp
Regular Visitor
5 years ago

Thx for your quick response but i don´t get it☹️

i tried to create a table with 3 columns (PartNumber, 2020, 2019) to apply your code but i can only create a table where PartNumber is first column and 2020 and 2019 are in the same column below each other and to next to them. what i´m doing wrong?

 

tenfingers's avatar
tenfingers
Advocate II
5 years ago

Sorry I probably didn't explain very well and you can disregard the Sumx part of my post as it's not relevant to your requirements. No need to create a new table. To calculate the difference between the 2019 qty and the 2020 qty you just need to create new measure that minuses one from the other. The measure should be in this format:

CALCULATE( SUM( 'FactTable'[Qty column]), 'DateTable'[Year] = 2020) -

CALCULATE ( SUM( FactTable[Qty column]), 'DateTable'[Year] =2019)

 

where FactTable is the name of your fact table and DareTable is the name of your date table assuming that you have a model setup in a star schema

  • Thomas_Ramhapp's avatar
    Thomas_Ramhapp
    Regular Visitor
    5 years ago

    tenfingers Anonymous amitchandak 

    Thanks Guys for your help. I found a very simple soloution from my problem.

    i mades some new basic measures.

     

    1. i summarized all the sales =SUM(TotalSales[Qty])

    2. Qty Last Year = CALCULATE(TotalSales[Qty], SAMEPERIODLASTYEAR (Date[Date]))

    3. Differnece from This year to Last year = IF( ISBLANK ([QtyLY]) , BLANK(), [TotalSales]-[QtyLY] )

     

     

    result fits perfect for me.

     

    Thx again

    regards Tom

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Thomas_Ramhapp,

      Glad to hear you find and share the solution here, I think it should be helpful if someone else has faced a similar issue and try to find out the suggestions on power bi community.😊

      Regards,

      Xiaoxin Sheng