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 in total numbers

 

need help please

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

     

8 Replies

  • tenfingers's avatar
    tenfingers
    Advocate II
    5 years ago

    If you would like to calculate the difference between two columns in your underlying table, you can use SUMX:

     

    SUMX(

    Table,

    'Table'[Column 1] - 'Table'[Column2]
    )

     

    SUMX goes line by line through the table (iterates) and minuses column 2 from column 1 for each row then sums the amount. For each row in the visual the sum is just the result of the current row but for a row total at the bottom of the visual , it is the sum of all the differences from each row.


    If you have years as a column and have a measure as your calculation then you can use the following:

     

    CALCULATE ( [Measure], Date[Year] = 2020) -

    CALCULATE ( [Measure], Date[Year] =2019)

    • 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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Thomas_Ramhapp , You need to create measures for that. Row-level calculations are not supported in Power BI

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Average PY = IF([Average CY]=0,BLANK(),CALCULATE([Average CY],SAMEPERIODLASTYEAR('dCalendar'[Date])))

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

    diff = [This Year]-[Last Year ]

     

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

    • Thomas_Ramhapp's avatar
      Thomas_Ramhapp
      Regular Visitor
      5 years ago

      maybe i miss something. i can add a quickmeasure which shows me the differnence in Qty  YoY in %

      but i´m not able to have q quickmeasure for difference in Qty YoY in total numbers?

      here is the code for qty in % can i tweak this code? the result of the code below are %

       

      Menge YoY% =
      VAR __PREV_YEAR = CALCULATE(SUM('StatVerkauf'[Menge]), DATEADD('Datum'[Date], -1, YEAR))
      RETURN
          DIVIDE(SUM('StatVerkauf'[Menge]) - __PREV_YEAR, __PREV_YEAR)