Received vs Closed against Year/Month

[Anonymous]

How to get the count of received and closed tickets per month/year based on the sample data below?

Ticket	Received date	Closed date
Ticket1	01-Jan-2017	02-Jan-2017
Ticket2	03-Jan-2017	01-Feb-2017
Ticket3	05-Jan-2017
Ticket4	01-Feb-2017	04-Feb-2017
Ticket0	29-Dec-2017	11-Jan-2017

Expected result:

Year	Month	Received	Closed
2016	December	1	0
2017	January	3	2
2017	February	1	2

Appreciate the help!

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

According to your description above, you should be able to follow steps below to get your expected result.

1. Create an individual Calendar table if you don't have it yet.

Date = CALENDAR("2016/01/01","2017/12/31")

2. Use formula below to  create "Year" column, and "Month" column in the Calendar table.

Year = YEAR('Date'[Date])

Month = FORMAT('Date'[Date],"mmmm")

3. Create two relationships between your table and the Calendar table with "Received date" and "Date"(active), "Closed date" and "Date" (inactive).

4. Use formula below to create different measures to calculate "Received" and "Closed".

Received = COUNTA(Table1[Received date])

Closed = CALCULATE(COUNTA(Table1[Closed date]), USERELATIONSHIP(Table1[Closed date],'Date'[Date]))

5. Show "Received" and "Closed" with "Year" and "Month" column in a Table visual.

Here is the sample pbix file for your reference.

Regards

[Anonymous]

@v-ljerr-msft and @tringuyenminh92 thanks for the help!

[tringuyenminh92]

Hi @Anonymous,

Cause your data structure has 2 date columns so i will unpivot these column by Query Editor:

Edit Queries -> Transform tab -> Unpivot columns -> rename result to Action and Date columns

Create calculated column for showing Month-Year from Date column:

Month_Year = Data[Date].[Month] & "-" & Data[Date].[Year]

(if Date column does not support .[Month] and .[Year], you could use Month() and Year() function to extract these values)

Create calculated column for order column

MonthYearNo = Data[Date].[MonthNo] + Data[Date].[Year]*100

Choose Month_Year column and sort by column MonthYearNo

In case you want to show Received and Closed values without "date" in the end, you could add one more transform step: (right click Action column -> choose Replace value -> input "date" and empty

Result with matrix control: