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
Anonymous
Not applicable

Create measure of (turnover year over year on a month level) calculations using revenue table

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 formula is that:  calculate(Revenue 2022 (Jan, Feb, March, ...) - Revenue 2021 (Jan, Feb, March, ...)/Revenue 2021 (Jan, Feb, March, ...))
  • same formula for 2022 vs. 2020 --> calculate(Revenue 2022 (Jan, Feb, March, ...) - Revenue 2020 (Jan, Feb, March, ...)/Revenue 2020 (Jan, Feb, March, ...))

The revenue table looks like that: -

Year-MonthRevenue
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

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Nawaz_0-1660050317505.png

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish_Mathur, 

Bundle of thanks, your provided solution is very near to my end goal. 

BR,

Nawaz

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

You can use quick measures for YoY computations. Have you tried that?

 

lbendlin_0-1659918367434.png

 

Anonymous
Not applicable

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.

Nawaz_1-1660050550901.png

 

 

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.