cancel
Showing results for
Did you mean:
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.

Regards,

Renato França

1 ACCEPTED SOLUTION

Accepted Solutions
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.
2 REPLIES 2
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.
Helper I

Hi @v-yuezhe-msft..

After some tests. That's Okay now.

Very thanks!!

Regards,

Renato França

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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