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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rohampourmehr
Frequent Visitor

YoY Calculations

I have the following three measures:

 

  1. MM Total Revenue All Time = SUM('Order Line'[Total Revenue])
  2. MM Total Revenue Last Year Calc = CALCULATE([MM Total Revenue All Time],SAMEPERIODLASTYEAR('Order Line'[Created Date].[Date]))
  3. MM Revenue YoY Change = DIVIDE( [MM Total Revenue All Time] - [MM Total Revenue Last Year Calc] , [MM Total Revenue Last Year Calc])

I've Created the chart below by graphing the MM Revenue YoY Chang:

Image 1.png

My questoin =>

2018 Qtr. 1 bar is calculating the entire revenue for 2017 Qtr.1 when running the MM Total Revenue Last Year Calc measure.

 

I would like this last bar to show the YoY as of Today's date. As an Ex if today is Jan 21st, I would like this chart to show the following calculation:

 

[ (2018 Revenue from Jan 1st to Jan 21st) - ( 2017 Revenue from Jan 1st to Jan 21st) ] / ( 2017 Revenue from Jan 1st to Jan 21st)

Thank you,
Roham

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this

 

  1. Create a calendar table by using the following formula under Modelling > New Table

=CALENDAR(MIN('Order Line'[Created Date]),MAX('Order Line'[Created Date]))

 

  1. Create a relationship between the Created Date column of the Order Line table to the Date column of the calendar table
  2. In the calendar table, create colums for Quarter, Month, year etc.
  3. In the visual, drag the time period (Year or quarter or month) from the Calendar Table
  4. Write these measures:

MM Total Revenue All Time = SUM('Order Line'[Total Revenue])

MM Total Revenue Last Year Calc = CALCULATE([MM Total Revenue All Time],SAMEPERIODLASTYEAR(Calendar[Date]))

MM Revenue YoY Change = DIVIDE( [MM Total Revenue All Time] - [MM Total Revenue Last Year Calc] , [MM Total Revenue Last Year Calc])

 

Hope this helps.

 


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

View solution in original post

3 REPLIES 3
AndreOlha
Frequent Visitor

@Ashish_Mathur  thanks for posting these formulas. They worked really well for me as well 🙂 

You are welcome.


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

Hi,

 

Try this

 

  1. Create a calendar table by using the following formula under Modelling > New Table

=CALENDAR(MIN('Order Line'[Created Date]),MAX('Order Line'[Created Date]))

 

  1. Create a relationship between the Created Date column of the Order Line table to the Date column of the calendar table
  2. In the calendar table, create colums for Quarter, Month, year etc.
  3. In the visual, drag the time period (Year or quarter or month) from the Calendar Table
  4. Write these measures:

MM Total Revenue All Time = SUM('Order Line'[Total Revenue])

MM Total Revenue Last Year Calc = CALCULATE([MM Total Revenue All Time],SAMEPERIODLASTYEAR(Calendar[Date]))

MM Revenue YoY Change = DIVIDE( [MM Total Revenue All Time] - [MM Total Revenue Last Year Calc] , [MM Total Revenue Last Year Calc])

 

Hope this helps.

 


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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.