Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
@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
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.
@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.
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
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
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
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 )
)
Best Regards
Rena
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.
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
@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.
@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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Hi @ghouse_peer
what do you mean average? Whats parameter do you want to count? average by what fields?
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.
@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
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k I have used both measures but values what i am getting is wrong, there is lot of difference. Kindly help
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
50 | |
38 | |
38 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |