Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Carried/Brought Forward Tickets - Excluding rows when counting based on a date period

Good morning,

I am fairly new to DAX so I am trying my best not to get confused with all of this :smileyhappy:

I am trying to create a Support Desk report which allow the team to see something like this:

2019-09-15 01_23_34-Report Viewer_ _Monthly Service Report - SILVER.png

I managed to create the following matrix using a few fields in the incidents table "Created on, Breached F.R. On, Breach R. On,":

2019-09-15 01_27_33-MSR - Silver V4 - Excep DFE Edition - Power BI Desktop.png

So far, no problems and data looks correct however, I want to add two more columns to the matrix above which are:

  • Brought Forward tickets "Active tickets from the month before the last one, brought into last month"
  • Carried Forward tickets "Active tickets in last month carried over into current month"

I used the following measure to calculate Active tickets at the end of each calendar month:

Active_tickets = 
ADDCOLUMNS (
    FILTER (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        DAY ( [Date] ) = 1
    ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMMM" ),
    "OpenTicket", CALCULATE ( COUNT ( incidents[incidentid] ))
        - CALCULATE (
            COUNT ( incidents[incidentid] ),
            FILTER ( Incidents, incidents[tiss_resolveddate] < [Date] )
        )
        + CALCULATE (
            COUNT ( incidents[incidentid] ),
               FILTER ( Incidents, ISBLANK( incidents[tiss_resolveddate] ))
        )
)

But I noticed that we have a major issue with our data where most of the historical records in our CRM system has no tiss_resolveddate value populated and the result was:

 

2019-09-15 01_41_16-MSR - Silver V4 - Excep DFE Edition - Power BI Desktop.png

Thus, I wonder if anyone can help me apply my following Ideas to overcome this issue or present any other idea that I can work with:

  1. Force the measure mentioned above to exclude any rows prior to a certain date "say prior to mid of 2017 won't be required".
  2. Amend the measure above to include the "Last_modifedon" date field and the State of the ticket field "Active or not" in order to identify when a ticket is closed rather than using the tiss_resolveddate.

I appreciate that my measure above might be totally wrong and irrelevant to the idea of calculating both Brought Forward tickets and Carried Forward tickets values.

 

Thanks

Hicham

Need Help

4 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Hichamissa ,

    I can't reporduce your design based on your description, so , if possible, could you please inform me more detailed information(suc as your sample data and your expecting output)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Zoe,

      Thank you for looking into this.

      Hi, 

      Thanks for your feedback on this and I apologise for the late reply.

      You will find a pbix file at this link: 

      https://tisskilimited-my.sharepoint.com/:f:/g/personal/hicham_issa_tisski_com/EtQ-maTkv0ZHunjqUh17WWABxpLRY-o1rD4sMK7hW-9wgA?e=UhPuRq

      I am trying to calculate the quantity of active tickets at the end of each month 30th or 31th depending on the calendar month number.

      In an ideal situation a ticket is considered de-activated/closed when a resolution date is populated into the resolution field however, where I work we have a historical data situation where a ticket is never being populated with a resolution date value upon closure.

      I tried creating a few dax measures and the best one I managed to create "included in the file" lead me to project the right quantity of all active tickets but only for the current month "Sep19" and not the historical one

      beside my measure above is showing all active tickets on the first day of a month and not the end of the month which is not correct as we might raise new tickets on the very first day of any month I need the dax to populate all active tickets at the end each month:

      Active_tickets_modifiedon1 =
ADDCOLUMNS (
    FILTER (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        DAY ( [Date] ) = 1
    ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMMM" ),
    "OpenTicket", CALCULATE ( COUNT ( incidents[incidentid] ),
            FILTER ( Incidents, incidents[modified on] < [date] ),
            FILTER(incidents, incidents[status] = "Active")
    )
)

      Any help would be much appreciated :)

      Kind regards

      Hicham

      • dax's avatar
        dax
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Hichamissa, 

        I am not clear abour your requirement, did you want to only show 67 in 2019 Sep row, other rows show 0? If so, you could refer to my below sample. If not, please correct me and inform me more detailed information (such as your expected output)

        create column like below

         

        Column = STARTOFMONTH(incidents[Modified On].[Date])

        then create relationship and measure like below

        Measure = CALCULATE(count(incidents[incidentid]), FILTER(incidents,(incidents[Status])="Active"))+0

         

         

        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.