Forum Discussion
YoY Calculations
I have the following three measures:
- MM Total Revenue All Time = SUM('Order Line'[Total Revenue])
- MM Total Revenue Last Year Calc = CALCULATE([MM Total Revenue All Time],SAMEPERIODLASTYEAR('Order Line'[Created Date].[Date]))
- 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
Hi,
Try this
- Create a calendar table by using the following formula under Modelling > New Table
=CALENDAR(MIN('Order Line'[Created Date]),MAX('Order Line'[Created Date]))
- Create a relationship between the Created Date column of the Order Line table to the Date column of the calendar table
- In the calendar table, create colums for Quarter, Month, year etc.
- In the visual, drag the time period (Year or quarter or month) from the Calendar Table
- 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.
3 Replies
Hi,
Try this
- Create a calendar table by using the following formula under Modelling > New Table
=CALENDAR(MIN('Order Line'[Created Date]),MAX('Order Line'[Created Date]))
- Create a relationship between the Created Date column of the Order Line table to the Date column of the calendar table
- In the calendar table, create colums for Quarter, Month, year etc.
- In the visual, drag the time period (Year or quarter or month) from the Calendar Table
- 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.
Ashish_Mathur thanks for posting these formulas. They worked really well for me as well 🙂
You are welcome.
