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

Plotting 2 dates from same data table on a line and stacked column chart

Data sample.PNG

 

 

How do I plot this on a line and stacked column chart, so the closed date is accurately represented?

When I create it with the open date as the 'shared axis', the prioirty as the 'column series' and the closed date as the 'line value'; the closed date is not being plotted against the month it actually closed because the axis is based on the open date. How do I get around this?

I almsot need to have two axis with some data plotting against the open date and some against the closed.

Here is what the graph currently looks like using my full report but the line series isnt correct.

Sample graph.PNG

Thanks

 

 

1 ACCEPTED SOLUTION

Hi @SEliza86

 

I have created a PBIX file using your data here

 

https://1drv.ms/u/s!AtDlC2rep7a-oDq7OZESviMpetzu

 

Basically it uses the data you provided in a table.  I then create two relationships to a separate date table.  One relationship handles the Opened Date (the default active relationship), while the 2nd relationship handles the count of the Closed date

 

I use a Month column from the Date table in my axis and plot as per my PBIX file.  I hope it makes sense.


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

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
Phil_Seamark
Employee
Employee

Hi @SEliza86

 

Any chance you can paste your data in as text to save typing it all out by hand?


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

Proud to be a Datanaut!

Project numberOpened dateClosed datePriority
67751/06/201730/06/20173
67761/06/201730/06/20173
67771/06/20171/11/20173
67781/06/201730/10/20173
67791/07/201730/07/20171
67801/07/2017 2
67811/07/20171/11/20175
67821/07/201730/10/20174
67831/07/201730/07/20174
67841/08/201715/11/20175
67851/08/201731/08/20173
67861/08/20171/09/20173
67871/08/20171/09/20173
67881/08/20171/09/20173
67891/09/20171/10/20173
67901/09/20171/10/20173
67911/09/201715/09/20172
67921/10/201730/10/20171
67931/10/201730/10/20174
67941/10/201730/10/20174
67951/10/201715/11/20174
67961/11/2017 5
67971/11/2017 1
67981/11/201715/11/20172
67991/11/201715/11/20173
68001/11/201715/11/20173
68011/11/20175/12/20173
68021/11/20175/12/20173
68031/12/2017 3
68041/12/2017 3

Hi @SEliza86

 

I have created a PBIX file using your data here

 

https://1drv.ms/u/s!AtDlC2rep7a-oDq7OZESviMpetzu

 

Basically it uses the data you provided in a table.  I then create two relationships to a separate date table.  One relationship handles the Opened Date (the default active relationship), while the 2nd relationship handles the count of the Closed date

 

I use a Month column from the Date table in my axis and plot as per my PBIX file.  I hope it makes sense.


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

Proud to be a Datanaut!

Thank you so much, I have been looking for hours for this solution!!

Anonymous
Not applicable

Hi Phil, 

 

I used your formula to create a Date table

Dates = ADDCOLUMNS(
 
CALENDARAUTO() ,
"MonthID" , INT(FORMAT([Date],"YYYYMM")),
"Month" , FORMAT([Date],"MMM YY"))
 
However, it gives me a huge range of date. From 2006 - 2100. May I know how do I limit the time period to just 2019 - 2022? 

Thanks a lot for this solution. I was looking for it from a long time.

That's awesome, thanks!

 

However, when I apply this logic to a different but similar data set I dont get the historic monthly view, just one big column:

Capture1.PNG

The only difference I can see is the open date in my new data set are second specific. Here's an example of the dates in my new data set (could the relationships in the table no longer be working cause the new open dates are so random?):

Open Date
3-Dec-2017 12:35:44 PM
3-Dec-2017 5:03:25 AM
29-Nov-2017 10:48:19 AM
28-Nov-2017 11:14:53 AM
28-Nov-2017 9:29:32 AM
27-Nov-2017 10:02:21 AM
22-Nov-2017 5:00:21 PM
22-Nov-2017 10:49:02 AM
21-Nov-2017 4:39:51 PM
20-Nov-2017 12:35:07 PM
15-Nov-2017 2:45:40 PM
13-Nov-2017 4:17:59 PM
13-Nov-2017 3:23:45 PM
13-Nov-2017 12:25:54 PM
10-Nov-2017 2:18:24 PM
10-Nov-2017 1:48:33 PM
10-Nov-2017 11:31:30 AM
10-Nov-2017 10:42:30 AM
10-Nov-2017 10:40:20 AM
9-Nov-2017 2:43:12 PM
9-Nov-2017 10:18:25 AM

 

 Thanks

Are the hours/minutes important?  Otherwise convert the column to be using DATE instead of DateTime.


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

Proud to be a Datanaut!

They're not important.

What's the quickest way to convert the column to just DATE?

 

Thanks!

In the Query Editor, you can transform the column to be Date rather than DateTime.  There should be a button called "Date" where you can choose a "Date only" option.  This will strip away the hours, mins and seconds


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

Proud to be a Datanaut!

Thanks Phil. I've learned heaps and all my reports are now working as intended.

I should add, that changing the format of the dates to just DATE from DATETIME in modelling hasnt changed the output of the graph. Its still just one big column not showing movement over the previous months.

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.