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

[Anonymous]

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.

[Anonymous]

Hi @Anonymous ,

Did the above solution solve your problem?

Best Regards,

Jay

[DataZoe · Accepted solution]

@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).

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.

[Anonymous]

Many Thanks DataZoe 🙂

[amitchandak]

@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]

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.