cancel
Showing results 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.

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:

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
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
3 REPLIES 3
Frequent Visitor

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Announcements

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

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors