Counting number of tickets opened in a month

[#1]

Hi!

I have been trying to find an answer without a success from the community. My problem is that I have a list of tickets that have been opened during the past six months. I need a table that shows how many tickets were opened last month per category. My fields are:

• Resolution Category Tier 1
• Submit Date
• Incident ID

Background information: There are plenty of other date based columns in the data as well.

1) With the following expression I managed to get all the tickets per Resolution Category Tier 1.

count({<[Resolution Category Tier 1]={Työasemainfra}>}[Incident ID])

2) I tried the following expression to filter only tickets opened in February, but I the answer is 0.

count({<[Resolution Category Tier 1]={Työasemainfra},[Submit Date]={">=1.2.2017<=28.2.2017"}>}[Incident ID])

3) I tried the following expression to filter only tickets opened in February, but I the answer is same as in case 1).

count({<[Resolution Category Tier 1]={Työasemainfra},date={">=1.2.2017<=28.2.2017"}>}[Incident ID])

What should I do?

Bonus question: Is there a way to automatize this so that I don't need to change the dates every month for a new report?

BR Tiia

[Sergey_Shuklin]

Hello, Tiia!

Can you provide some data sample (scrambled or manually generated), which will fit your original data. Beacause there are multiple solutions depend on which data you have.

[#3 · Accepted solution]

Here's how I solved this:

Measure:

count({<[Resolution Category Tier 1]={NameOfTheCategory}

, [IncidentReportedMonth] = {"$(=$(vPreviousMonth))"}

}>}[Incident ID])

Variable:

vPreviousMonth=MonthStart((AddMonths(Today(),-1)))

In Data Load Editor:

monthstart(date([Submit Date])) as [IncidentReportedMonth]

     where [Submit Date] is column name where ticket's opening date is.