Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pankajdhillon
Frequent Visitor

Year over Year in line chart

Does anyone have idea how we can show 2 (or more) years of data as separate lines in line chart. When I try to use Year (using DAX FORMAT funtion) of a transaction date as "Legend" (and transaction date column as Axis) it still shows data in contionous form rather than showing one line over the other line. Any help is greatly appreciated.

1 ACCEPTED SOLUTION
pankajdhillon
Frequent Visitor

First of all thanks everyone for the reply and the solution proposed by mike_honey is the one which I had in mind but was looking if there was any better way to do it. So finally what I did was to create a new column as

Transaction Day=FORMAT(table[transactionday],"MM-dd"), without year and then used it as Axis and "Year" as legend which makes the grapgh to look like this, which is not perfact as it shows each day as one data point and for whole year graph looks too long but it's better than nothing

 

LineChart.png

View solution in original post

9 REPLIES 9
pankajdhillon
Frequent Visitor

First of all thanks everyone for the reply and the solution proposed by mike_honey is the one which I had in mind but was looking if there was any better way to do it. So finally what I did was to create a new column as

Transaction Day=FORMAT(table[transactionday],"MM-dd"), without year and then used it as Axis and "Year" as legend which makes the grapgh to look like this, which is not perfact as it shows each day as one data point and for whole year graph looks too long but it's better than nothing

 

LineChart.png

Did you ever manage to fix this showing "normal" date visually? kind of like the attached pic?

sdrakou_0-1646420602738.png

 

Hi all,

 

I am facing the same issue pankajdhillon was having. However, the proposed solution does not solve my problem. 

 

I want to show the daily y-o-y growth rate of number of transactions for several years on the same chart. First, I calculate the total number of transactions as a new measure:

 

Total Volume = CALCULATE(SUM(CombinedData[Volume]))

 

Using this new measure, I create a measure to obtain sum of transactions for previous year:

 

Volume LY = CALCULATE([Total Volume], DATEADD('CombinedData'[Sales ISODate],-1, YEAR))

 

Finally, I obtain the y-o-y growth rate:

 

Volume % = DIVIDE(([Total Volume]-[Volume LY]),[Volume LY], BLANK())

 

 

Now, if I put Volume % and Transaction Day (mm-dd) on the same chart, I get an error saying that "Function DATEADD only works with contiguous date selections". The same happens, if I use SAMEPERIODPREVIOUSYEAR function to calculate Volume LY. 

 

The chart does not give error but stays blank, if I calculate Volume LY like this:

 

Volume LY = SUMX(CombinedData, CALCULATE([Total Volume], DATEADD(CombinedData[Sales ISODate], -1, YEAR)))

 

Could you please help me understand where the problem is? Thanks a lot in advance!

kcantor
Community Champion
Community Champion

If you are going to show the data, you may as well build the dax so that you can use the data you show. I would add a datedim table and create measures to show last years sales and 2 year prior sales. Once you create those they will render on your chart and be usable to calculate YOY sales variance and % growth as well.

I prefer the DateAdd function for these instead of Previous year so that I can make simple changes to the dax and pull separate years for as far back as I want.

Once the measures are created simply add them to the values section instead of using years.

If you need help with the dax just give us an idea as to what your data looks like.

For me, I do Sum of sales as SUM([TotalSales]) and filter my report to the current year. Then I build the other using calculate ---  Last Year Sales = CALCULATE((SUM[TotalSales], DATEADD([DimDate[Date],-1,year))

From there it is easy for YOY -- YOY=[TotalSales]-[LastYearSales]

%Growth = DIVIDE([YOY],[LastYearSales],"-")

You can go as far back as you want by changing the -1 in Dateadd to however many years you want to go back.

Using this method you can display by week, month, or quarter by simply changing the Axis. The measures remain the same and the visual changes.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




fbrossard
Advocate V
Advocate V

The best way is to use a time dimension, in order to have all the attributes you need for your analysis.

Let's have an example :

create a time dimension = { dateKey datetime, year int, month int, monthName nvarchar(20) ....}

and a fact table such as orders = {orderKey, dateKey, storeKey, ... , amount}

And you just have to set your line graph like this :

  • axis = monthName
  • legend = year
  • values = amount

Thank a lot! 

 

mike_honey
Memorable Member
Memorable Member

I think you also need to calculate a "Date in Single Year" column, to present all the month and day values as if they fell in a single year.

 

Here's a rough example:

 

Date in Single Year = DATEVALUE( FORMAT ( TODAY(), "YYYY-" ) & FORMAT( Events[Date] , "MM-DD" ) )

 

Then use that Field for the Axis.

 

This works fairly well, as long as you are happy to ignore the year in the X-Axis.  There doesnt seem to be any way to control that at this point.

 

This might be too crude - I can imagine issues with end-of-February dates and Leap Years for example.  But hopefully it gets you moving.

mike_honey, 

This was very helpful for my Year On Year graph, except for the fact that I now have a disturbing but carefully chosen year (leap year) on the x-axis.

 

Is there any way to remove this year? I tried changing the format of the Date In Single Year Column, and even if it is displayed as I want to in the Data View Page, when I choose the format to be d.MMMM, it still shows the year on the line graph. Anyone knows how to handle this? 

 

Thanks! 

I use Julian Date on the X axis (new column) and then have the calendar date as a tooltip value when I need to show years stacked on the same graph.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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