Need help to create DAX formula for monthly backlog ticket calculation.

[HarshalK]

Hi All,

I'm beginer in Power BI and currently working on Ticket count query. I need to calculate backlog (Which will Carry Fwd for next month) w.r to Service and store on monthwise table. 

I have created new table for this calculation as given below.

I can see there were mismatch in backlog calculation while trying to use your DAX formula.

e.g: For Service 'A'  there is actual start of service "A" is from Mar-16 so previous value showing 0 for all columns.

       As per DAX formula for Service "A" for Mar-16

       Inflow =6
      Closed = 3 then Backlog should be : Carry_Fwd (0)+ Inflow (6) - Closed (3) = Backlog (3)  but DAX formulation value showing 4.

Same for Apr-16: if logically Backlog considering 4 as Carry_Fwd of last month 4+9-3 = 10 but it showing 9.

I have tried create below DAX formula and calculate.

----------------------------------------

Backlog =

var _ser = NEW_MOR[Service]

var _date = NEW_MOR[End_Date_Month]

var Carr_Value = CALCULATE (

COUNTROWS ( Sheet1 ),

Sheet1[Service]= _ser,

Sheet1[Opened] <= _date, OR(Sheet1[Closed] > _date, ISBLANK ( Sheet1[Closed]) ) )

RETURN

IF(ISBLANK(Carr_Value),0,Carr_Value)

----------------------------------------

Could you please help me Backlog formula so I can create another table of Carr Fwd. By considering old month Backlog = next Month Carry Fwd?

I'm new in Power Bi and still learning for better understing.

Thanks in advance.

Kind Regards,

Harshal Kulkarni 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Yiannis_N]

Hello mr Ashish,
i have found your response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i think your method of solving the problem is close to the one i m looking for, but in my attempt to download the pbi file and see the calculation, the file is missing.

Futhermore some more information on what i need to achieve and how the solution you provided is close but needs something more to be completed.
The Final Output of the report i m trying to implement is the following. The Backlog calculation should provide a cummulative sum of the tickets that remain open (Status = Open) and be added on the following month.

For example: 

On September 2023 Open Tickets  (45) - Closed Tickets (23) + Backlog (1) = 23.
On your previous response regarding the backlog calculation, i notice that the dax formula doesnt take in consideration the previous backlog tickets if on the previous month none was opened or complete.

Year	Month	Opened_Tickets	Closed_Tickets	Backlog
2023	June	1	0	1
2023	July	3	3	1
2023	August	0	0	1
2023	September	45	23	23
2023	October	68	51	40
2023	November	72	38	74
2023	December	59	36	97
2024	January	127	69	155
2024	February	135	64	226
2024	March	104	105	225
2024	April	216	120	321
2024	May	138	237	222
2024	June	5	5	222

A few things on my raw data and tables that i need to do the calculation on.
Table 1 = Jira Raw that contains the following Columns: 

Key	Status	Created	Resolved
Τckt-1043	Open	3/6/2024 16:41
Τckt-1042	Open	3/6/2024 14:30	5/6/2024 14:30
Τckt-1041	Open	3/6/2024 12:43

My calendar Table is called Calendar [Date] that contains year,quarter,month, week and weekday.

My relationships look as follows : 

Active relationship between Calendar [Date] and Jira raw [created]
Inactive relatonship between Calendar [Date] and Jira raw [Resolved] 

As far as my calculations :

Opened_Tickets = if(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))), 0, CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created])))

Closed_Tickets = IF(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))),0,CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved])))

Backlog = CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Created]  <= max('Calendar'[Date]) || not(ISBLANK(Jira_Raw[Created])))) - CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Resolved]  <= max('Calendar'[Date]) && not(ISBLANK(Jira_Raw[Resolved]))))

Last but not least i m new to the whole dax and power BI world so any help on achieving the correct calculation of the backlog should be much appreciated.

Looking forward hearing from you.

Best Regards.

[Anonymous · Accepted solution]

Hi @HarshalK ,

Based on your description, you can create a measure as follows.

Backlog = SUMX(FILTER(ALL('Table'),[Service]=SELECTEDVALUE('Table'[Service])&&[Date]<=SELECTEDVALUE('Table'[Date])),[Inflow]-[Closed])

Test table：

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HarshalK]

Could you please help me with my latest data ?

Post (3 of 4)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[HarshalK]

Hello Ashish,

There is another case which I have missed to update you on post. Actually data is as below given format and which I need to use to calculate actually monthly count

And I need to create Summary Table like:

 Where 

Carry_Fwd = Backlog of last month (Which will get consider 0 on initial level)

Backlog    = Carry_Fwd + Open - Closed

Could you please help me with this output?

I had tried but still facing issue with Service wise sort out.

Kind Regards,

Harshal Kulkarni

[Ashish_Mathur]

Hi,

How is this one different from your initial post?  Is my solution not working?

[HarshalK]

Hello Ashish,

Thank you for your message.

Your previous solution is working but that table I have created using actual data table data as shown above. I have created new table using below steps.

First three column summerized :

SUMMARIZECOLUMNS(DateTable[Year],DateTable[Month],DateTable[End_Date_Month],Sheet1[Service])

Inflow & Closed : Used basic filters to calculate this number.

Carry_Fwd : Tried to create new one using calculated inflow,closed column as per suggestion.

But I would like to calculate this SR count monthwise as I shown in above table. So my dashbaord display will be like:

Kind Regards,

Harshal

[Ashish_Mathur]

Hi,

I do not recommend creating calculated tables because they do not respond to filter/slicer selections.  I recommend building actuals tables/visuals in which you drag fields from your source data tables.

[HarshalK]

Hello Ashish,

Could you please little ellaboarate and help me with your suggestion?

Actually I would like to understand best practice and correct way to fulfil the requirment.

Many thanks for your suggestion and guidance.

Kind Regards,

Harshal Kulkarni

[Ashish_Mathur]

Hi,

Please review the solution which i had shared with you previously.  I have not used the SUMMARIZECOLUMNS() function at all.  The result of the SUMMARIZECOLUMNS() is a calculated table which does not respond to filter/slicer selections.  My solution involved writing measures which return scalar values.

Once again, please review the solution which i shared with you previously.