How to Create a YOY Trend?

[Anonymous]

I am trying to find a new way of formatting/manipulating my data in order to produce a YOY Trendline. The transaction dates run for example for event 1: April 2017 - December 2018 and event 2: April 2018 - December 2019. Currently the work-around I am using is I am essentially splitting the first part of the event,  April 2017 - December 2017 (April 2018 - December 2018) and renaming the year to 2000, and the remaining part of the event to 2001, then using the event name as the legend. Is there a better work-around where I am able to do this without including the year?

[amitchandak]

How would like to display it finally. can you share some examples or screenshot. Not very clear as of now.

[Anonymous]

I am hoping to do it from a Monthly view, so for example, sales from April 2018 - December 2019, where it compares it YOY with events that occurred in the same 21 month period in previous years, so:

• April 2018 - December 2019
• April 2017 - December 2018
• April 2016 - December 2017

[amitchandak]

Based on page 17, can you set up an example , what is needed
https://www.dropbox.com/s/unfqa025ca7p3vw/CompareRange_timedim.pbix?dl=0

[Anonymous]

Hm, I'm not 100% sure I can set up an example here, its almost like grouping by [Color Year] but looking at the whole life cycle of the color. So for [Color Year] = yellow, the months are from Jan-2017 to Dec-2018, but for [Color Year] = lightgreen the months are from Jan-2015 to Dec-2016. So being able to compare the life cycles of [Color Year] = yellow and [Color Year] = lightgreen on a bar chart, January (2015 vs 2017) to December (2016 vs 2018). 

[Anonymous]

Please see my examples below. Sorry for the horrible picture formatting. But essentially I want to create the first picture, which is using my current workaround, but I was wondering if there was a bettter way of doing it since there are months where the events overlap.

The current workaround I am using, formatting the dates to 2000 and 2001No workaround used.

[amitchandak · Accepted solution]

Hope you had created a date dimension and Month year view is coming from there. These measures should help. YTD can work LTYD.

And Normal measure should get potted with a year behind measure

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))

LYTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))

Year Behind measure= CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601