Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Chart backlog with conditions

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.

 

picture.jpg

Regards,

Renato França

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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

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

View solution in original post

3 REPLIES 3
mamsteroonie
Helper I
Helper I

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.

 

mamsteroonie_0-1625148962688.png

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
Employee
Employee

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

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

 Hi @v-yuezhe-msft..

 

After some tests. That's Okay now. 

 

Very thanks!!

 

Regards,

 

Renato França

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.