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
jgenchanok
Frequent Visitor

Overlaying Graphs

I have a set of monthly cost data over the course of several years. If, for example, I want to plot cost over time for January-June of 2014, is there a way to overlay a plot on top of that for January -June of 2015, to compare trends over the two years? Is this done in the reports or can it be done on a dashboard as well? 

1 ACCEPTED SOLUTION

Hi @jgenchanok

 

In answer to your most recent question, I generated some dummy data as follows

 

Date	Usage
1/01/2014	10
1/02/2014	21
1/03/2014	29
1/04/2014	36
1/05/2014	44
1/06/2014	44
1/07/2014	47
1/08/2014	51
1/09/2014	65
1/10/2014	72
1/11/2014	82
1/12/2014	93
1/01/2015	104
1/02/2015	117
1/03/2015	114
1/04/2015	129
1/05/2015	143
1/06/2015	138
1/07/2015	141
1/08/2015	149
1/09/2015	155
1/10/2015	163
1/11/2015	169
1/12/2015	171
1/01/2016	180
1/02/2016	178
1/03/2016	186
1/04/2016	191
1/05/2016	191
1/06/2016	191
1/07/2016	190
1/08/2016	191
1/09/2016	200
1/10/2016	211
1/11/2016	218
1/12/2016	229

Which gave me three years.

 

I then added the following two calcualated columns to my table

 

Month = FORMAT('Table9'[Date],"MMMM")

MonthID = INT(FORMAT('Table9'[Date],"MM"))

The MonthID is just there to help sort the [Month] column in order.  The table now looks like this

 

Month.png

 

I can then make Matrix and Line visuals as follows

 

pivot.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @jgenchanok,

 

Yes this is possible.  I suggest you create two measures.  The first will be simple and might be the sum of cost,  The second will be similar but use one of the Time-Intelligence functions like SAMEPERIODLASTYEAR to build a second measure that you can add to your visual.

 

If you post a small set of your data we can try and build something for you.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks Phil, 

I was wondering if it had similar functionality to creating a pivot table in excel. For example, in excel, I can create a pivot table that has months as rows and years as columns and it will plot those years individually.  Capture.PNG

 

ICapture_2.PNG

If I try to make a similar format matrix table in Power, it does not correlate to the same type of graph if I wanted to make a combo chart. 

Hi @jgenchanok

 

In answer to your most recent question, I generated some dummy data as follows

 

Date	Usage
1/01/2014	10
1/02/2014	21
1/03/2014	29
1/04/2014	36
1/05/2014	44
1/06/2014	44
1/07/2014	47
1/08/2014	51
1/09/2014	65
1/10/2014	72
1/11/2014	82
1/12/2014	93
1/01/2015	104
1/02/2015	117
1/03/2015	114
1/04/2015	129
1/05/2015	143
1/06/2015	138
1/07/2015	141
1/08/2015	149
1/09/2015	155
1/10/2015	163
1/11/2015	169
1/12/2015	171
1/01/2016	180
1/02/2016	178
1/03/2016	186
1/04/2016	191
1/05/2016	191
1/06/2016	191
1/07/2016	190
1/08/2016	191
1/09/2016	200
1/10/2016	211
1/11/2016	218
1/12/2016	229

Which gave me three years.

 

I then added the following two calcualated columns to my table

 

Month = FORMAT('Table9'[Date],"MMMM")

MonthID = INT(FORMAT('Table9'[Date],"MM"))

The MonthID is just there to help sort the [Month] column in order.  The table now looks like this

 

Month.png

 

I can then make Matrix and Line visuals as follows

 

pivot.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.