backlog of tickets

[DiKi-I]

How I can calculate the backlog of tickets. Whenever a new tickets comes its should be added in the open tickets. Bascially I want the cumulative of open tickets and closed tickets should be subtracted. I have creation date with active relationship with date and closed date withb inactive relation with date.

[DiKi-I]

@lbendlin 

[lbendlin]

Your backlog is off by one.

You cannot have daily backlog as your source data only has monthly granularity.

How would the yearly backlog look like?  At the end of the year?

[DiKi-I]

Thanks for your input on this with the measure which I was using was causing one off. I have a date dimension and I changed your measure as below but visual is not loading.

Backlog Measure =

var d = max('Calendar'[Year Month Name])

var t = filter(all(Incidents),RELATED('Calendar'[Year Month Name])<=d)

return sumx(t,[Total Incidents])-sumx(t,[Incidents Resolved])


---Measure which I was using previously but its one off not sure why --: I have active relationship with date based on creation date and inactive relation based on resolve date.

Backlog =

var em = ENDOFMONTH(calendar[Date])

var f = filter(all(Incidents),Incidents[created_date]<=em)

var g = filter(all(Incidents),coalesce(Incidents[resolved_date],em+1)<=em)

return countrows(f)-countrows(g)

[DiKi-I]

Can you help mewith what I am doing wrong?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[DiKi-I]

Can you help with this why its one off.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[DiKi-I]

Hi ,

I have uploaded the data:

https://docs.google.com/spreadsheets/d/1He3b7-NzyrsX5UrXtFzpnggDLDSxOISO/edit?usp=sharing&ouid=10852...


I want cumulative to work at daily,monthly or yearly grain

[Ashish_Mathur]

Your data the file has absolutely no semblence to the description in the original post.  In the original post, you mentioned that there are 2 date columns whereas in the file you just have one.  Ensure that the description and data you share are coherent.