cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

Accepted Solutions
Microsoft
Microsoft

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

2 REPLIES 2
Microsoft
Microsoft

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

 Hi @v-yuezhe-msft..

 

After some tests. That's Okay now. 

 

Very thanks!!

 

Regards,

 

Renato França

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors