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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.