Chart backlog with conditions

[renatofrancavr]

Hi,

I have a volumetric chart (Opened tickets, Closed tickets and Backlog) by day in the Excel that feeds the PowerBi, the data are cumulative. I need to consider in the formulas of opened, closed and backlog the following condition: Status column <> Cancelled and Test Column <> No. Follow the picture below with the formulas and the chart.

Note: The columns: Opened, Closed and Backlog are in the calendar table and columns Status and Test are in another table.

Regards,

Renato França

[mamsteroonie]

I am doing the same thing and everything works great until the opened date is null and the closed date is not null.  Then the backlog ignores the closed number and does not calculate.  Does this have something to do with the open date being a primary relationship with the calendar table?  The running total in this screenshot should be 9, not 11.  It's not counting the -1.  I'm almost thinking the open - closed formula is doing a - negative which is cancelling it out.  When there is a number in the opened column, and the backlog result is negative, it calculates.

Opened = COUNTA(workorder_dim[reportdate])

Closed = CALCULATE(COUNTA(workorder_dim[actfinish]),USERELATIONSHIP(dates_dim[calendardate],workorder_dim[actfinish]),workorder_dim[actfinish]<>BLANK())

RT Backlog = CALCULATE([Opened]-[Closed],FILTER(ALL(dates_dim),dates_dim[calendardate]<=MAX(dates_dim[calendardate])))

@v-yuezhe-msft 

[v-yuezhe-msft · Accepted solution]

Hi @renatofrancavr,

From your screenshot, it seems that Table contains Opened date column and Resolved date column, and you create relationships using the date columns between Table and CalendarTable, right?

If that is the case, you can firstly create new measures using the following formulas.

Opened1 = CALCULATE(COUNTA(Table[Opened date]),FILTER(Table,Table[Status]<>"Cancelled" && Table[Test]<>"No"))

Resolved1 = CALCULATE(COUNTROWS(FILTER(Table, Table[Status]<>"Cancelled" && Table[Test]<>"No")),USERELATIONSHIP(CalendarTable[Date],Table[Resolved date]))

Balance1 = [Opened1]-[Resolved1]

Then create new measure of RT Balance according to Balance1. If you don't get expected result from the above formulas, please help to post sample data of your tables.

Thanks,
Lydia Zhang

[renatofrancavr]

 Hi @v-yuezhe-msft..

After some tests. That's Okay now. 

Very thanks!!

Regards,

Renato França