Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm a newbie in power bi and would like to create a Turnover year-over-year on a month-level measure by using a revenue table.
The revenue table looks like that: -
Year-Month | Revenue |
Feb-20 | $1,240,364 |
Mar-20 | $1,541,278 |
Apr-20 | $1,553,634 |
May-20 | $1,733,675 |
Jun-20 | $1,760,203 |
Jul-20 | $1,761,163 |
Aug-20 | $1,724,403 |
Sep-20 | $1,543,193 |
Oct-20 | $1,652,241 |
Nov-20 | $2,092,541 |
Dec-20 | $2,084,639 |
Jan-21 | $2,381,285 |
Feb-21 | $1,857,632 |
Mar-21 | $4,283,196 |
Apr-21 | $2,090,327 |
May-21 | $2,095,656 |
Jun-21 | $1,626,479 |
Jul-21 | $1,522,725 |
Aug-21 | $1,587,332 |
Sep-21 | $1,403,103 |
Oct-21 | $1,370,082 |
Nov-21 | $1,449,310 |
Dec-21 | $1,396,600 |
Jan-22 | $1,418,506 |
Feb-22 | $1,173,422 |
Mar-22 | $1,508,759 |
Apr-22 | $1,291,598 |
May-22 | |
Jun-22 | |
Jul-22 | |
Aug-22 |
The result i want to achieve looks like that in percentage, the formulas I'm using: -
Turnover YoY 2022 vs. 2021 | Turnover YoY 2022 vs. 2020 | |
January | -40% | |
February | -37% | -5% |
March | -65% | -2% |
April | -38% | -17% |
May | ||
June | ||
July | ||
August | ||
September | ||
October | ||
November | ||
December |
I tried earlier with a couple of different ways but was not able to reach the end output.
Like, i created Current year and Last year, respectively, with this formula:
Revenue CY = CALCULATE(
SUM(Table[Revenue]),
'Date'[Year] = YEAR(TODAY())
)
Revenue LY = CALCULATE(
SUM(Table[Revenue]),
'Date'[Year] = YEAR(TODAY())-1
)
Can you please guide me what the best way to achieve it by using measures?
@lbendlin , @amitchandak , @SpartaBI, @tamerj1
Thanks in advance.
Regards,
Ahsan
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Ashish_Mathur ,
Bundle of thanks for your response. But how can we compare data 2020 vs. 2022?
Moreover, I would like to create these calculations on separate measures because my end goal is to generate a line chart out of these calculations. The result should look like that.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish_Mathur,
Bundle of thanks, your provided solution is very near to my end goal.
BR,
Nawaz
You are welcome.
You can use quick measures for YoY computations. Have you tried that?
Hi @lbendlin , thank you for your response.
No, i haven't tried quick measure, but i can work on it.
My actual goal is to create these calculations on separate measures to create a line chart out of these calculations. The result should look like that.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |