Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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] )
result fits perfect for me.
Thx again
regards Tom
@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 %
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
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?
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] )
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
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |