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
Anonymous
Not applicable

How to plot a clustered graph with Opened & Closed Dates from 2 different sheets

Hi, I have 2 reports - Opened on last 6 months & Closed on Last 6 months. There could be few incidents not in Opened sheet and vice versa.  I want to plot both the values in one graph. Below i have already treid.

 

Unpivot column Opened & Closed in above sheets and append the 2 tables. Now created a relationship of the date with calender table and created below measures - 

 

CountCases = DISTINCTCOUNT(Append1[Number])
CreatedCases = Calculate([CountCases],Append1[Type] ="Opened")
ClosedCases = Calculate([CountCases],Append1[Type] ="Closed")
 
Now trying to plot the graphs with year month from Calender table in Axis and CreatedCases & ClosedCases in Values however getting blank in the axis in the graph.
 
 
1 ACCEPTED SOLUTION
DataZoe
Employee
Employee

@Anonymous As you have stacked the two tables into one, you can create a date table as @amitchandak has pointed out or simply create calculated column that pulls out the month date for each of your datetimes, and use that as your axis.

 

Month =
DATE ( YEAR ( [DateTime] ), MONTH ( [DateTime] ), 1 )

 

This will turn all the dates in Feburary to the first of the month to aggegrate them into a single month. This will make sure it's sorted correctly and give you options to have the chart as a continuous axis (so it won't scroll like categorical).

 

DataZoe_2-1616083153018.png

 

 

If you do choose to make it categorical, then you can format the month correctly in the format of that column in the ribbon, or in the modeling view even choose a custom format, such a mmm 'yy.

 

DataZoe_1-1616082916384.png

 

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Did the above solution solve your problem?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
DataZoe
Employee
Employee

@Anonymous As you have stacked the two tables into one, you can create a date table as @amitchandak has pointed out or simply create calculated column that pulls out the month date for each of your datetimes, and use that as your axis.

 

Month =
DATE ( YEAR ( [DateTime] ), MONTH ( [DateTime] ), 1 )

 

This will turn all the dates in Feburary to the first of the month to aggegrate them into a single month. This will make sure it's sorted correctly and give you options to have the chart as a continuous axis (so it won't scroll like categorical).

 

DataZoe_2-1616083153018.png

 

 

If you do choose to make it categorical, then you can format the month correctly in the format of that column in the ribbon, or in the modeling view even choose a custom format, such a mmm 'yy.

 

DataZoe_1-1616082916384.png

 

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

Many Thanks DataZoe 🙂

amitchandak
Super User
Super User

@Anonymous , check if these dates have timestamp. because in that case join will fail

 

you have to create date like

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

and then join

refer to this video, I have shown how to check date has timestamp: https://www.youtube.com/watch?v=OBf0rjpp5Hw

 

Change measure like

CreatedCases = Calculate([CountCases], filter

( Append1,Append1[Type] ="Opened"))
ClosedCases = Calculate([CountCases],

filter

( Append1,

Append1[Type] ="Closed"))

 

Anonymous
Not applicable

Hi Amit,

 

Thanks for the advice. I tried above however it dint work. Somehow appending 2 tables created issues with the date field. I had to Split date column and convert it into numbers and then merge again to fix the issue. It worked with the same date column i had in my sheet. 

 

Many thanks for your help, learnt something new.

 

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.