Averege count of tickets for past six months from current month

[ghouse_peer]

Hello Guys,

I have open date column and ticket number column. I want to show, last 6 months average opened tickets in card  visual.

Details: Table name: Tickets. 

             Column name: Opened Date, Ticket Number

Kindly Help me with the solution. Thanks in advance.

[amitchandak]

@ghouse_peer , you have take count and divide by 6 like

Rolling 6 = CALCULATE(count(Table[Ticket]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Table[Open Date]),-6,MONTH)) /6
Rolling 6 = CALCULATE(count(Table[Ticket]),DATESINPERIOD('Date'[Date ],MAX(Table[Open Date]),-6,MONTH))  /6

[ghouse_peer]

Hello @amitchandak I have used your calculation, but getting different value.

Both opened and closed tickets are showing the same value. Kindly help. I have provided the sample data in msgs kindly check and help me with the solution.

[ghouse_peer]

@amitchandak  I have used 2nd measure, I am getting the value. Will this work for closed tickets too? Because scenario is same i need to get Last 6 months average tickets closed count. I tried both measures using closed date but getting wrong. Kinldy help.

[v-yiruan-msft]

Hi @ghouse_peer ,

Could you please provide some sample data about closed tickets information? Whether it is also from Tickets table? And did you get any error message when used the measure provided by @amitchandak get wrong? If yes, please provide the related screen shot. Or you mean the returned value of measure for calculating closed tickets count is not correct? Thank you.

Best Regards

Rena

[ghouse_peer]

Hello @v-yiruan-msft Sample data as follows:

Tickets

Table Name: Tickets
Ticket Opened Date	Ticket Closed Date	Ticket Number
1/29/2020	7/1/2020	1122
1/2/2020	6/10/2020	2233
2/15/2020	6/10/2020	1313
1/12/2020	6/15/2020	1212
2/15/2020	6/15/2020	1111
2/29/2020	6/2/2020	3131
2/1/2020	6/2/2020	3434
1/1/2020	6/6/2020	3535
2/2/2020	6/6/2020	3232
2/3/2020	6/6/2020	3233

Dates

Table Name: Dates

Dates

1/29/2020

1/2/2020

2/15/2020

1/12/2020

2/15/2020

2/29/2020

2/1/2020

1/1/2020

2/2/2020

2/3/2020

Above are the 2 tables. Tickets and Dates. Dates table is created using range of dates and it contains dates till 31/12/2020.

So now the task is: In a card visual 1st card: I need to show last 6 months average opened tickets.

                                                        2nd card:  I need to show last 6 months average Closed tickets.

Kindly help with the solution

[v-yiruan-msft]

Hi @ghouse_peer ,

Please follow the below steps to achieve it or you can refer my sample pbix file to do it.

1. Create relationships between Tickets and Dates base on date fields as below screen shot

Create relationshis

2. Create measures to get the average of open/close tickets count

Average opened tickets = 
DIVIDE (
    CALCULATE ( DISTINCTCOUNT ( 'Tickets'[ Ticket Number] ) ),
    DATEDIFF ( MIN ( Dates[Date] ), MAX ( 'Dates'[Date] ), DAY )
)

Average Closed tickets = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Tickets'[ Ticket Number] ),
        USERELATIONSHIP ( 'Dates'[Date], 'Tickets'[Ticket Closed Date] )
    ),
    DATEDIFF ( MIN ( Dates[Date] ), MAX ( 'Dates'[Date] ), DAY )
)

Create measures

Best Regards

Rena

[ghouse_peer]

Hello @v-yiruan-msft  i have created the relationships accordingly. But values are different.

1) I dont want to use slicer , by using measure its hsould show me last 6 months average of opened/closed tickets.

2) when i take tht measure in card it should calculate accordingly for last 6 months.

you hv taken only average not 6 months average.

Kindly help.

[v-yiruan-msft]

Hi @ghouse_peer ,

Please check if the following screen shot is what you want:

Average opened tickets = 
var _curdate=TODAY()
var _last6months=DATE(YEAR(_curdate),MONTH(_curdate)-6,DAY(_curdate))
return
DIVIDE(CALCULATE(DISTINCTCOUNT(Tickets[ Ticket Number]),FILTER('Tickets','Tickets'[Ticket Opened Date]>=_last6months&&'Tickets'[Ticket Opened Date]<=_curdate)),6)

Average Closed tickets = 
var _curdate=TODAY()
var _last6months=DATE(YEAR(_curdate),MONTH(_curdate)-6,DAY(_curdate))
return
DIVIDE(CALCULATE(DISTINCTCOUNT(Tickets[ Ticket Number]),FILTER('Tickets','Tickets'[Ticket Closed Date]>=_last6months&&'Tickets'[Ticket Closed Date]<=_curdate)),6)

If the above one is not your expected result, please provide the correct values based on your provide tables and the calculation logic(such as the average is base on Month or Day? ). Then we can provide you suitable formulas. Thank you.

Best Regards

Rena

[ghouse_peer]

@v-yiruan-msft  1) The provided data is sample data, the original data has the date and ticket numbers in same format as i provided.(Unable to attach excel file, if any way let me know).

2)The average is based on Month.
I want to show last 6 months average opened Tickets and Average closed tickets in 2 separate card visuals. For ex: Last 6 months average from July 1st.

I tried the calculation provided by you but i am getting different  value.

Kindly help.

[ghouse_peer]

@v-yiruan-msft  Yes it is also from 'tickets' table.

As u said  the returned value of measure for calculating closed tickets count is not correct.

I have used the 2nd measure for the closed tickets and i have changed only column name to closed date reamining expression is same, the return value is wrong. 

Kinldy help.

[parry2k]

@ghouse_peer As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

Add following measure

Tickets = COUNTROWS ( TableTickets )

Avg Last 6 Months = 
AVERAGEX ( DATESINPERIOD ( DateTable[Date], MAX ( DateTable[Date] ), -6, MONTH ), [Tickets] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ghouse_peer]

@parry2k  I already have table Dates in my model.

According to the calculation provided by you. Countrows[Tabletickets] will count the number of rows present in the table.

I am getting solution with lot of difference.

My task is to show 6 month average tickets opened count. Kindly help.

Details: 1) Table: Dates( Contains all dates from the model, created by calendarauto())

             2)Table: Tickets.  Columns: opened Date, Ticket Number

[az38]

Hi @ghouse_peer 

what do you mean average? Whats parameter do you want to count? average by what fields?

[ghouse_peer]

hello @az38 I mean average by ticket Number. Particular ticket will be opened on particular date and same goes for closed date.

Tickets

Table Name: Tickets
Ticket Opened Date	Ticket Closed Date	Ticket Number
1/29/2020	7/1/2020	1122
1/2/2020	6/10/2020	2233
2/15/2020	6/10/2020	1313
1/12/2020	6/15/2020	1212
2/15/2020	6/15/2020	1111
2/29/2020	6/2/2020	3131
2/1/2020	6/2/2020	3434
1/1/2020	6/6/2020	3535
2/2/2020	6/6/2020	3232
2/3/2020	6/6/2020	3233

Dates

Table Name: Dates

Dates

1/29/2020

1/2/2020

2/15/2020

1/12/2020

2/15/2020

2/29/2020

2/1/2020

1/1/2020

2/2/2020

2/3/2020

So according to the 'Ticket opened date' and 'ticket Number' i want last 6 six months Average opened tickets.

same according to the 'Ticket Closed date' and 'ticket Number' i want last 6 six months Average Closed tickets.

I dont want slicer to filter dates. When i take the value in card visual it should show the calculated value for last 6 months. Kindly help with the solution.

[ghouse_peer]

@az38  I mean average by Ticket opened date. for ex: we are in June. Last 6 months average opened ticket count i need to show.

Details i have provided. FYI  I have seperate Date table which contains all dates from the model. Created by using Calendarauto().

Kindly help

[parry2k]

@ghouse_peer did you looked at the full expression There are two measures, did you applied 2nd measure in my original post.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ghouse_peer]

@parry2k  I have used both measures but values what i am getting is wrong, there is lot of difference. Kindly help