Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to count the count of ticket Closed Last Month and Current Month (Count of Tickets closed Closed Date < Due Date) / Count of TicketID ),
Kindly find the Below Table
i have used
Solved! Go to Solution.
@Anonymous
Created the calendar table and relate it with Completed date. Then use following measures:
Tickets Closed =
COUNTROWS(dtTable)
Ticket Closed (Before Due Date) =
CALCULATE(
[Tickets Closed],
FILTER(
dtTable,
dtTable[ClosedDate] < dtTable[DueDate]
)
)
Ticket Closed (Prev Month) =
CALCULATE(
[Tickets Closed],
PREVIOUSMONTH(ftCalendar[Date])
)
Ticket Closed (Before Due Date, Prev Month) =
CALCULATE(
[Ticket Closed (Before Due Date)],
PREVIOUSMONTH(ftCalendar[Date])
)
% Closed (CM) = DIVIDE([Ticket Closed (Before Due Date)],[Tickets Closed])
% Closed (PM) = DIVIDE([Ticket Closed (Before Due Date, Prev Month)],[Ticket Closed (Prev Month)])
Sol pbix file here
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hello @Anonymous ,
You may try this:
Sample Data table:
Add a calendar table:
ftCalendar =
ADDCOLUMNS(
CALENDARAUTO(),
"Year",YEAR([Date]),
"Month",EOMONTH([Date],-1)+1,
"QTR","Q" & FORMAT([Date],"Q")
)
You may refer to the article for more details: Calendar Table
Create relationships between the data table and calendar table
Add following measures:
Ticket Created (Current Month) =
COUNTROWS(dtTable)
Tickets Closed =
CALCULATE(
[Ticket Created (Current Month)],
USERELATIONSHIP(ftCalendar[Date],dtTable[ClosedDate])
)
Ticket Closed (Prev Month) =
CALCULATE(
[Tickets Closed],
PREVIOUSMONTH(ftCalendar[Month])
)
Exceeding Due Date =
VAR _Filter =
CALCULATETABLE(
FILTER(
dtTable,
dtTable[DueDate] < dtTable[ClosedDate]),
USERELATIONSHIP(ftCalendar[Date],dtTable[DueDate])
)
VAR _Count =
COUNTROWS(_Filter)
RETURN
_Count
You will get the following result
Solution PBIX file here
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top :)(Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Visit blog: vivran.in/my-blog
Feel free to email me for any BI needs .
Connect on LinkedIn
Follow on Twitter
@Anonymous
Ideally, it should open as I can access the file using the same link.
Reposting the link
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Feel free to email me for any BI needs.
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
@Anonymous
Follow the calendar creation process and link it with the data table as suggested in the post earlier.
Then use the following measures:
Ticket Closed (Current Month) =
VAR _Filter =
FILTER(
dtTable,
dtTable[Type] = "Account"
&& dtTable[ClosedDate] < dtTable[DueDate]
)
VAR _Count =
CALCULATE(
COUNTROWS(dtTable),
_Filter
)
RETURN
_Count
Ticket Closed (Prev Month) =
CALCULATE(
[Ticket Closed (Current Month)],
PREVIOUSMONTH(ftCalendar[Month])
)
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
it's Not working kindly find below condition
Last Month = if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed last Month)
CurrentMonth = if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed Current Month)
@Anonymous
It is not clear what do you mean by "/" in you condition.
Can you tell me the expected output (in a form of table) from the example dataset you have shared?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Kindly Download the Sample Result File (i have mentioned conditions and sample Results)
Condition:
Current Month = (if type=Account and (Closed date < Due date) divided by no of Ticket ID closed by Current Month)
Last Month = (if type=Account and (Closed date < Due date) divided by no of Ticket ID closed by Last Month)
@Anonymous
Created the calendar table and relate it with Completed date. Then use following measures:
Tickets Closed =
COUNTROWS(dtTable)
Ticket Closed (Before Due Date) =
CALCULATE(
[Tickets Closed],
FILTER(
dtTable,
dtTable[ClosedDate] < dtTable[DueDate]
)
)
Ticket Closed (Prev Month) =
CALCULATE(
[Tickets Closed],
PREVIOUSMONTH(ftCalendar[Date])
)
Ticket Closed (Before Due Date, Prev Month) =
CALCULATE(
[Ticket Closed (Before Due Date)],
PREVIOUSMONTH(ftCalendar[Date])
)
% Closed (CM) = DIVIDE([Ticket Closed (Before Due Date)],[Tickets Closed])
% Closed (PM) = DIVIDE([Ticket Closed (Before Due Date, Prev Month)],[Ticket Closed (Prev Month)])
Sol pbix file here
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |