Adding late backlog measure to cumulative backlog chart

[thawkins87]

I've had success creating a running backlog chart a la the solution offered here:

https://community.powerbi.com/t5/Desktop/Cumulative-backlog-to-date/td-p/35469

But now I need to figure out how to add a new measure to plot the number of items in that monthly backlog that were past due in any given period, or, if it's easier, the average age of each item in the backlog at the end of each given time period. Sample picture below:

Using the following measures, again, from the previous solution:

• REQUESTS_CLOSED = COUNTA('Test Request Data'[PERIOD_COMPLETED])

• REQUESTS_CREATED = CALCULATE(COUNTA('Test Request Data'[PERIOD_CREATED]),USERELATIONSHIP('Calendar LOOKUP'[Date],'Test Request Data'[Created]))

• Backlog = [REQUESTS_CREATED]-[REQUESTS_CLOSED]

• Running Backlog = CALCULATE([Backlog],filter(all('Calendar LOOKUP'),'Calendar LOOKUP'[FY_PERIOD]<=max('Calendar LOOKUP'[FY_PERIOD])&&NOT(ISBLANK('Calendar LOOKUP'[FY_PERIOD]))))

It's easy enough to calculate the current number of items in the backlog that are late, but I need to go back and look at the "Running Backlog" for each fiscal period and filter out/count just the items where the Period End Date was greater than the Requested Due Date from my data table.

I've thought myself in circles on this and I wouldn't think it would be this difficult. Any help is appreciated.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.  Show the expected result in a simple Table format.  Once the result in the table are correct, we cal shift to any other visual.

[Anonymous]

Hi,

https://docs.google.com/spreadsheets/d/1EptxTcwNEkyjGwI7_A3jLGH7l25IV2P3/edit#gid=1346702304

Would this data help? I have been struggling for a while trying to do exactly what @thawkins87 tried to do, but I did not see a solution for his inquiery.

[Ashish_Mathur]

Hi,

I cannot understand what you want.  Since you mention that your formulas did not work properly, it implies that the results in range L:W are incorrect.  Show the Excel formulas in that range so that i can translate that logic into the DAX language.

[Anonymous]

Nevermind my poor explanation.

Let's just use the data I presented, the ones that has a Records with record ID, Created Date, Due Date, and Closed Date, to do what the original submitter, @thawkins87 , asked.

In his or her request (and even attached an image), the idea is to show each month the following:

- How many were created that month (Date is within the 1st and last day of that month; or eomonth(createddate,0)=eomonth(CurrentMonth,0) <== remember, we are looking for a snapshot of each of the past 12 months, per the image

- How many were closed that month (eomonth(closeddate,0)=eomonth(CurrentMonth,0))

     - How many were closed that month late (Date is within that month, AND the Due Date is less than Closed date)     AND(eomonth(closeddate,0)=eomonth(CurrentMonth,0),closeddate>Duedate)

- How many were Carryover or backlog from previous months (The Created Date is previous to the first day of current month, and the closed date is either empty or bigger/equal to the first day of the current month). This shows that the record was not closed in the previous month and, therefore, was carried over to the current month. 

The formula I have to count for the whole month is:

=SUMPRODUCT(--(RecordsTable[Created Date]<A3),--(RecordsTable[Date Closed]>=A3))+SUMPRODUCT(--(RecordsTable[Created Date]<A3),--(RecordsTable[Date Closed]="")), where A3 is the start of each month.

Does that help?

- 

[v-yingjl]

Hi @thawkins87 ,

 "how to add a new measure to plot the number of items in that monthly backlog that were past due in any given period"

How did you define the given period monthly, by date slicer or anything else? If it is checked by the slicer, I think 

Backlog = [REQUESTS_CREATED]-[REQUESTS_CLOSED]

could already get the result monthly.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.