Backlog- count a ticket in each month it is not solved

Hi, I want to count ticket in each month it is not solved

I used following formula which gives the correct results but the drill through is incorrect
attached an example of what I wanbacklog voorbeeld.xlsx (11.7 KB)

backlog = var datemax = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
    SUMX(
        FILTER(
            'FNincidentCases'
            , 
            ('FNincidentCases'[Calendar_CreateDate] <= datemax && ISBLANK('FNincidentCases'[Calendar-Total_Solution Provided Date]))
            ||
            ('FNincidentCases'[Calendar_CreateDate] <= datemax && 'FNincidentCases'[Calendar-Total_Solution Provided Date] > datemax)
        ) 
        , 1
    )
    , ALL('Calendar'[Date]) 
    , CROSSFILTER('Calendar'[Date], 'FNincidentCases'[Calendar_Final_Sol_Completion_Date] , None)
    , CROSSFILTER('Calendar'[Date] , 'FNincidentCases'[Calendar_CreateDate] , None)
)

Hi @hoevekr, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Hi @hoevekr,

You can check this link to guide your solution

Best,

DAVID.

Hi David,

The solution that you provided is not what I am looking for. In the meantime I managed to calculate the backlog but overdue which I thought should be measured in the same way be it with other dates is not doing what I expected. I made a pbix file with the 2 calculation in. It would be very helpful if you can take a look. More explanation is added in the a pbix filebacklog - overdue.pbix (334.7 KB)

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

1 Like

Hi, thank for the Dax clean up tool I know this tool.
Can some one help me with question on the overdue calculation. It is explained the PBIX file

Kind regards,
Kristel

Hi @hoevekr, from your question, I understood that you want to “count ticket in each month it is not solved”.
Your required output looks like:
image

Dax, I have used for it:
Tickets Not solved =
CALCULATE(
COUNT(FNincidentCases[Support Ticket Number]),
filter(FNincidentCases, FNincidentCases[final_solution_Provided_Perf_count]=0))

[Note: if you want to count distinct ticket, Dax will be changed to distinctcount]

Feel free to share if it works :slight_smile:

Regardsbacklog - overdue.pbix (331.3 KB)

Hi, @ sheik_mishuk thank you for looking into my question but it is not what I need.
I need to report the ticket in each month it is overdue:
each ticket have a target date and a solution date.
both dates can be blank a target date when the ticket has no target we don’t have to take into account and a blank solution date when no solution is been provided
I need to make a trend report so in each month the ticket is overdue it should be counted

  • target date 202001 tickets solved in 202005 It is overdue for 4 months. I need to count it in
    202001, 202002, 202006 and 202004

Hi @hoevekr, the way I understood, you want to count tickets which missed target date of solution.
To count that:
First I have made a “Calculated column- Overdue Status”-
Overdue Status =
if(FNincidentCases[Solution Provided Date]>FNincidentCases[Reported Date],“Yes”, “No”)

Then I have created the measure of OverdueCount-
OverdueCount =
CALCULATE(count(FNincidentCases[Support Ticket Number]),
FILTER(FNincidentCases, FNincidentCases[Overdue Status]=“Yes”))

It’s finally returning your expected result as below:
image

Would be great if I am able to solve your query with this. If you are still expecting something different, please upload a screenshot of your expected outcome [may be a dummy on excel].

Regards

backlog - overdue.pbix (332.1 KB)

Hi @hoevekr, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!

Hi,
Also the last solution is not what I am looking for
a ticket is overdue if solution date > target date
a ticket has to be counted in each month it is overdue, in the month the ticket is solved it should not be counted
in the data source are ticket without target those should be excluded

example: target date solved 2020-01 2020-02 2020-03 2020-04 2020-05 2020-06

case 1 2020-02 2020-04 1 1

case 2 2020-01 2020-01 not counted as target date and solved are in the same month

case 3 2020-01 2020-02 1

case 4 2020-01 not solved 1 1 1 1 1 1

Hi @hoevekr:

I have updated the dax as per your condition: This is the main dax which actually defining the overdue status. So I am setting condition what I am able to interpret from your queries.
[if the target date is null, it’s excluded from the calculation, I have counted tickets not days, Overdue will be counted if it passed the target day, not target month]
Overdue Status =
if(ISBLANK(FNincidentCases[Calendar_Final_Sol_Target_Date]),“No”,
if(DATEVALUE(FNincidentCases[Solution Provided Date])>DATEVALUE(FNincidentCases[Calendar_Final_Sol_Target_Date]),“Yes”, “No”))
[I used the Datevalue dax as without it the time value also being counted and you will get overdue despite you deliver on the dame day]

But in some cases, I have confusion about the dataset, like-
In the below table, the solution date is well ahead of target but the solution provided count is 0. So that column I have not used in setting conditions. Because it’s conflicting.

Also, the active relationship is logical between Date & Target Date:
[existing inactive relationship seems redundant to me]

Final outcome will be like below:

image backlog - overdue.pbix (336.9 KB)

Please let me know if it serves the purpose now.

Hi @hoevekr, we’ve noticed that no response has been received from you since the 18th of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!