Forum Discussion
How to Create a YOY Trend?
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?
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-functionsAppreciate 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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
6 Replies
How would like to display it finally. can you share some examples or screenshot. Not very clear as of now.
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.
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-functionsAppreciate 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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Based on page 17, can you set up an example , what is needed
https://www.dropbox.com/s/unfqa025ca7p3vw/CompareRange_timedim.pbix?dl=0