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
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
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.