Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thomas_Ramhapp
Regular Visitor

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

SharedScreenshot.jpg

need help please

1 ACCEPTED SOLUTION

@tenfingers @v-shex-msft @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] )

 

SharedScreenshot.jpg

result fits perfect for me.

 

Thx again

regards Tom

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@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

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)

 

SharedScreenshot.jpg

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
tenfingers
Advocate II
Advocate II

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)

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?

SharedScreenshot.jpg

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

@tenfingers @v-shex-msft @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] )

 

SharedScreenshot.jpg

result fits perfect for me.

 

Thx again

regards Tom

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.