YoY Calculations

[rohampourmehr]

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

[AndreOlha]

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

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

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.