DAX formula for Monthly Cumulative Backlog Ticket Calculation
Hello, i have found a 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 the method described 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. https://www.linkedin.com/safety/go?url=https%3A%2F%2Fcommunity.fabric.microsoft.com%2Ft5%2FDAX-Commands-and-Tips%2FNeed-help-to-create-DAX-formula-for-monthly-backlog-ticket%2Ftd-p%2F1651032&trk=flagship-messaging-web&messageThreadUrn=urn%3Ali%3AmessagingThread%3A2-OTkwNjEwMTYtOTMwNS00MzJkLThmNDQtMjMzNjUwNTFjYTEzXzAxMw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BlGvr8tgdR92f5F9Hpj%2BQRg%3D%3D 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. Best Regards.
Tickets Backlog Calculation through DAX
Based on the first 3 columns(Image Attached), I am calculating Backlog for each day in excel, now I am moving to Power BI and new here. Using DAX how can I calculate Backlog from the first 3 columns in Power BI. I have Incident ID, Created Date and Closed Date column , and I want Backlog of each day. Kindly suggest Thankyou
SAP Order Backlog over time until delivery date
Hi everybody, I am currently facing the challenge to show how my order backlog develops over time until the expected delivery date. I have SAP sales data where we have order intake postings as well as reduction postings when there was a sale for the order. I already have a measure that shows the order intake, one for the reduction postings and another one that shows the order backlog over time. However it continues until forever but I would want it to only show the order backlog until the expected delivery date (which is the posting date in the order entry row) for every order. How can I achieve this? My data looks like this. Order Posting Intake Date Posting Date Amount 12345 Order Entry 05.11.2022 24.01.2023 1000 12345 Reduction Posting 05.11.2022 10.12.2022 -700 In this case I would want to see an order backlog of 1000 from 05.11.2022 - 09.12.2022, then order backlog of 300 from 10.12.2022 - 23.01.2023. My measure already does most of it, but it shows the 300 until forever, not only until 23.01.2023. Somehow I need to tell the function to do this calculation for every order and then stop at the expected delivery date. But I don't know how... This is my code Order Bklog = CALCULATE( CALCULATE( [Order Int] + [Reduction Postings], 'Fact Order'[Source] <> "HIST"), FILTER( ALL('Dim Date'), AND( 'Dim Date'[Date] <= MAX('Dim Date'[Date]), NOT(ISBLANK('Dim Date'[Date]))))) Thanks in advance! Janina
