cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jyoti_Samuel
Regular Visitor

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
Super User II
Super User II

@Jyoti_Samuel 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,
DataZoe


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 @Jyoti_Samuel ,

 

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
Super User II
Super User II

@Jyoti_Samuel 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,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

Many Thanks DataZoe 🙂

amitchandak
Super User IV
Super User IV

@Jyoti_Samuel , 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"))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors