Forum Discussion

renatofrancavr's avatar
renatofrancavr
Helper I
9 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      renatofrancavr
      Helper I
      9 years ago

       Hi Anonymous..

       

      After some tests. That's Okay now. 

       

      Very thanks!!

       

      Regards,

       

      Renato França

       

  • mamsteroonie's avatar
    mamsteroonie
    Helper I
    5 years ago

    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])))
    Anonymous