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

     

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)

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Thomas_Ramhapp,

    You can take a look at the following links to know how to use the 'if statement' to find out the current row context level, then you can write a specific expression to replace the total level calculations results.

    Clever Hierarchy Handling in DAX 

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng