Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am looking for some help to solve the below problem:
Ticket_ID | Open_Date | Closed_Date |
1 | 01/01/2020 | 01/01/2020 |
2 | 01/12/2021 | 05/12/2021 |
3 | 01/13/2021 | 01/14/2021 |
4 | 01/14/2021 | 01/15/2021 |
5 | 01/15/2021 | 01/16/2021 |
6 | 01/16/2021 | 01/17/2021 |
7 | 01/17/2021 | 01/18/2021 |
8 | 01/18/2021 | 01/19/2021 |
9 | 01/19/2021 | 01/20/2021 |
10 | 03/20/2022 | 03/21/2022 |
11 | 03/21/2022 | 03/22/2022 |
12 | 03/22/2022 | 03/23/2022 |
From the above data set,
1.I need to calculate the Monthly Rolling Total for the Tickets in Open Status
2. Calculate the Monthly Rolling Total of the tickets in Closed status
3. I also need to plot the Monthly Rolling Totals for Open and Closed Tickets in Multiple Axes Line Graph.
Thanks in Advance.
Solved! Go to Solution.
I just realised you were after monthly rolling totals. Here are three measures which I believe will achieve this:
Monthly Rolling Total (Opened) =
// Number of tickets whose Opened_Date was in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Open_Date] >= _StartDate,
'TicketData'[Open_Date] <= _LastDate
)
Monthly Rolling Total (Closed) =
// Number of tickets whose Closed_Date was in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Closed_Date] >= _StartDate,
'TicketData'[Closed_Date] <= _LastDate
)
Monthly Rolling Total (Open) =
// Number of tickets whose status was Open in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
_StartDate <= 'TicketData'[Closed_Date],
'TicketData'[Open_Date] <= _LastDate
)
This is how I did it:
Create a date table:
DateTable = CALENDAR(DATE(2020,1,1), DATE(2022,12,1))
Create a calculated column in that table. This is just so you can slice by month (formatted as YYYY Mmm):
YYYY Mmm = FORMAT('DateTable'[Date], "Mmm YYYY")
Create three measures to calculate the number of tickets opened within any given period; the number of tickets closed within any given period; and the number of tickets that are still open for any given period.
TicketCount (Opened in Period) =
VAR _StartDate = FIRSTDATE('DateTable'[Date])
VAR _LastDate = LASTDATE('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Open_Date] >= _StartDate,
'TicketData'[Open_Date] <= _LastDate
)
TicketCount (Closed in Period) =
VAR _StartDate = FIRSTDATE('DateTable'[Date])
VAR _LastDate = LASTDATE('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Closed_Date] >= _StartDate,
'TicketData'[Closed_Date] <= _LastDate
)
TicketCount (Open During Period) =
VAR _StartDate = FIRSTDATE('DateTable'[Date])
VAR _LastDate = LASTDATE('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS('TicketData'),
_StartDate <= 'TicketData'[Closed_Date],
'TicketData'[Open_Date] <= _LastDate
)
To get your sample data in, I added a calculated table called TicketData:
TicketData =
DATATABLE(
"TicketID", INTEGER, "Open_Date", DATETIME, "Closed_Date", DATETIME,
{
{"1","01/01/2020","01/01/2020"},
{"2","01/12/2021","05/12/2021"},
{"3","01/13/2021","01/14/2021"},
{"4","01/14/2021","01/15/2021"},
{"5","01/15/2021","01/16/2021"},
{"6","01/16/2021","01/17/2021"},
{"7","01/17/2021","01/18/2021"},
{"8","01/18/2021","01/19/2021"},
{"9","01/19/2021","01/20/2021"},
{"10","03/20/2022","03/21/2022"},
{"11","03/21/2022","03/22/2022"},
{"12","03/22/2022","03/23/2022"}
}
)
And those measures seem to work when I drop them into a table (by Month)
And when charted by Month:
Hope this helps.
I just realised you were after monthly rolling totals. Here are three measures which I believe will achieve this:
Monthly Rolling Total (Opened) =
// Number of tickets whose Opened_Date was in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Open_Date] >= _StartDate,
'TicketData'[Open_Date] <= _LastDate
)
Monthly Rolling Total (Closed) =
// Number of tickets whose Closed_Date was in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
'TicketData'[Closed_Date] >= _StartDate,
'TicketData'[Closed_Date] <= _LastDate
)
Monthly Rolling Total (Open) =
// Number of tickets whose status was Open in the last 30 days prior to the current day
VAR _LastDate = LASTDATE('DateTable'[Date])
VAR _StartDate = DATEADD(_LastDate, -30, DAY)
RETURN
CALCULATE(
COUNTROWS('TicketData'),
_StartDate <= 'TicketData'[Closed_Date],
'TicketData'[Open_Date] <= _LastDate
)
Thanks for the solution, but this does not work when the date column has duplicate values.
E.g.: when I have 2 tickets for the same Open_Date, the above solution fails.
Can you please help how to resolve this scenario.
Thanks in advance.
@Anonymous Could you please post the sample data that shows this problem occurring? I have added an extra row of data to the original sample data you provided so that it contains a duplicate date, and the measures appear to be returning the correct number. TicketIDs 12 and 13 both have the same Open_Date and Close_Dates.
When I create a table visual with Date and the Monthly Rolling Total (Opened), for 22/03/2022 I get the correct count of 4 tickets (Ticket IDs 10, 11, 12, and 13 have an Open_Date from 20/02/2022 to 22/03/2022)
TicketData =
DATATABLE(
"TicketID", INTEGER, "Open_Date", DATETIME, "Closed_Date", DATETIME,
{
{"1","01/01/2020","01/01/2020"},
{"2","01/12/2021","05/12/2021"},
{"3","01/13/2021","01/14/2021"},
{"4","01/14/2021","01/15/2021"},
{"5","01/15/2021","01/16/2021"},
{"6","01/16/2021","01/17/2021"},
{"7","01/17/2021","01/18/2021"},
{"8","01/18/2021","01/19/2021"},
{"9","01/19/2021","01/20/2021"},
{"10","03/20/2022","03/21/2022"},
{"11","03/21/2022","03/22/2022"},
{"12","03/22/2022","03/23/2022"},
{"13","03/22/2022","03/23/2022"}
}
)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |