Forum Discussion

Anonymous

Not applicable

6 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...

Need Help

dax

Community Support

6 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

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
Community 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.
- Anonymous
  Not applicable
  6 years ago
  Good morning Zoe,
  Thanks again for your feedback on this.
  My primary aim was to aquire the count of active tickets on the very last day of each month, keeping in mind that I have no field that indicate a specific date for that step "closure" therefore, after a strugle I was lucky to reach this point below which is mirroring the right figures in total. Each row shows the full count for all customers. I have then added several other columns "Raised, Breached, Closed etc" all good so far.
  
  Brought_Carried_F = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2014, 1, 1 ), DATE ( 2025, 12, 31 ) ), DAY ( [Date] ) = 1 ), "YearMonth", FORMAT ( [Date], "YYYY-MMMM" ), "Date2", FORMAT ( [Date], "dd mmmm yyyy" ), "ClosedTicketsperMonth", CALCULATE ( COUNT ( incidents[incidentid] ), FILTER ( incidents, incidents[statecode] <> 0 ), FILTER ( Incidents, incidents[modifiedon] >= [date] ), FILTER ( Incidents, incidents[modifiedon] < EDATE([date], 1) ) ) )
  When I linked the Date in this table to my general date table "Date" that govern the whole report it seems to be missing out on the customers. e.g. Relationships form:
  
  As you can see that the customer filter is not having any affect on the table shown below:
  I think the original question I had in this post, is already resolved as per my code above, but a new follow up query has emerged on the back of that which relates to table relationships and how to make it work.
  
  Any Idea?
  
  Kind regards :)
  Hicham