Forum Discussion
Post PatronCount of Backlog data
Hi Team,
I have a table in which we have 4 columns named Ticket Number,Opened Date, Closed Date, State.
Ticket Number: Unique and may have different states based open Open and close.
Opened Date: Contains all dates of past 2 years to till date.
Closed Date: Contains all Dates from past 2 years to till date.
Stae: Open and Closed
Ticket Number Open Date Closed Date State
001 1/4/2022 Open
002 3/4/2022 Open
002 5/4/2022 Closed
001 5/4/2022 Closed
Question is i need backlog count and calculation is in this way: (Opened Ticket Count of current Month)-(Closed Ticket of Current Month)+ (Opened tickets of Previous months which are not closed in any months and is still open)
PLease help me with the solution
10 Replies
- amitchandak
Super User
ghouse_peerHi Amit Chandak,
Thanks for the files for reference.
Tried your calculation/DAX, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help
For ex: lets say earlier to March month Backlog was 100 after all calculations.
Now
March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170
April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265
This Backlog count i need, PLease help
- ghouse_peer
Hi amitchandak
Thanks for the input and files
Tried your calculation/DAX and referred to the files and tried, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help
For ex: lets say earlier to March month Backlog was 100 after all calculations.
Now
March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170
April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265
This Backlog count i need, PLease help
- SpartaBI
Community Champion
hey ghouse_peer I just solved something very similar to that in a differnet question.
https://community.powerbi.com/t5/Desktop/End-of-month-backlog/m-p/2469889#M880720- ghouse_peer
Hi Sparta,
Tried your calculation/DAX, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help
For ex: lets say earlier to March month Backlog was 100 after all calculations.
Now
March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170
April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265
This Backlog count i need, PLease help
- amitchandak
ghouse_peer , Please find attached file, you might need a small change
- ghouse_peer
Hi SpartaBI
Thanks for the input
Tried your calculation/DAX, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help
For ex: lets say earlier to March month Backlog was 100 after all calculations.
Now
March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170
April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265
This Backlog count i need, PLease help
- SpartaBI
hey ghouse_peer, try this (I still missing some info about how you want to actually present the result, but I hope you this would cover it, I gave you two variations of the first VAR depending on what - I guess - are your options):
Backlog = --choose one version of the next VAR: -- in case you have a matrix with months as the filetr contest: VAR _current_eom = EOMONTH( 'Table'[Date] ),0) ----------------------------- -- in case you have a card to just show the current month based on TODAY() function with no other date related filter context: VAR _current_eom = EOMONTH(TODAY(),0) ------------------------------- VAR _all = CALCULATE ( DISTINCTCOUNT ( 'Table'[Ticket Number] ), 'Table'[Open Date] <= _current_eom, REMOVEFILTERS('Table') ) VAR _resolved = CALCULATE ( DISTINCTCOUNT ( 'Table'[Ticket Number]] ), 'Table'[Closed Date] <= _current_eom && 'Table'[Closed Date] > 0, REMOVEFILTERS('Table') ) RETURN _all - _resolved
