Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello BI Group,
I am attempting to create a rolling day count of open & closed tickets, based on the fact that the
the open tickets date column is BLANK and the closed date field is not.
My first measure to count the number of closed tickets is (and is calculating the correct number) :
Solved! Go to Solution.
This solution is actually for calculation a rolling day count of Open and Closed tickets in a help desk system.
First, I had to create one measure to count the number of rows in the table helpdesk.
Two, I had to create a measure that counted the number of open tickets by their status' (status column)
( I had to dig in the data to see what ticket categories were for open, and then for closed tickets.
Open = Resolution Date is NOT populated.
Closed = A populated resolution date.
I had to create two DateDIM tables, one for open tickets, and the other for closed tickets.
You need a relationship between the DateDim table and the helpdesk table joined by the creation date.
You need a relationship from the second Date2Dim table and the helpdesk table's resolution date.
After creating the above measures, additional table, and the relationships, the below measures worked correctly.
HelpDesk Open 7 Days = CALCULATE([HelpDesk-CountOpenTx], DATESINPERIOD(DateDim[Date], MAX(DateDim[Date]), -7, DAY))
HelpDesk Closed 7 Days = CALCULATE([HelpDesk-CountClosedTx], DATESINPERIOD(DateDim2[Date], MAX(DateDim2[Date]), -7, DAY))
I hope this helps someone out in the future.
PR
This solution is actually for calculation a rolling day count of Open and Closed tickets in a help desk system.
First, I had to create one measure to count the number of rows in the table helpdesk.
Two, I had to create a measure that counted the number of open tickets by their status' (status column)
( I had to dig in the data to see what ticket categories were for open, and then for closed tickets.
Open = Resolution Date is NOT populated.
Closed = A populated resolution date.
I had to create two DateDIM tables, one for open tickets, and the other for closed tickets.
You need a relationship between the DateDim table and the helpdesk table joined by the creation date.
You need a relationship from the second Date2Dim table and the helpdesk table's resolution date.
After creating the above measures, additional table, and the relationships, the below measures worked correctly.
HelpDesk Open 7 Days = CALCULATE([HelpDesk-CountOpenTx], DATESINPERIOD(DateDim[Date], MAX(DateDim[Date]), -7, DAY))
HelpDesk Closed 7 Days = CALCULATE([HelpDesk-CountClosedTx], DATESINPERIOD(DateDim2[Date], MAX(DateDim2[Date]), -7, DAY))
I hope this helps someone out in the future.
PR
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
35 | |
28 | |
24 | |
24 |