Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating the Monthly Rolling Total For the Open And Closed Tickets

Hi All,
I am looking for some help to solve the below problem:

Ticket_IDOpen_DateClosed_Date
101/01/202001/01/2020
201/12/202105/12/2021
301/13/202101/14/2021
401/14/202101/15/2021
501/15/202101/16/2021
601/16/202101/17/2021
701/17/202101/18/2021
801/18/202101/19/2021
901/19/202101/20/2021
1003/20/202203/21/2022
1103/21/202203/22/2022
1203/22/202203/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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
        )

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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)

EylesIT_0-1653326137883.png

And when charted by Month:

EylesIT_1-1653326292833.png

 

 

 

Hope this helps.

Anonymous
Not applicable

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
        )
Anonymous
Not applicable

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
Not applicable

@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)

 

EylesIT_0-1653466698733.png

 

 

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"}
        }
    )

 

Anonymous
Not applicable

@Sattam Hope this helps

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.