Forum Discussion

yanx1990's avatar
yanx1990
Frequent Visitor
7 years ago
Solved

Backlog issue when filter by Date

Dear PBI Community,

 

Glad to be part of the Power BI users since a few weeks, I learned a lot about Power BI and already generated a lot of reports but today, I'm facing an issue and I can't find a solution.

 

I'm Incident Manager and I need a graph showing the Open / Closed tickets with the backlog (backlog meaning a picture of how many tickets where opened for a certain period).

 

I have the following data : 

 

With these data, I'm able to generate this graph :

 

The "Backlog Tickets" is a measure :

Backlog Tickets = 
VAR
    Incoming_Tickets = CALCULATE(
    COUNT(Fact_Incoming_Closed[Request ID]),
    FILTER(
        ALLSELECTED(Fact_Incoming_Closed),
        Fact_Incoming_Closed[Date]<=MAX('Fact_Incoming_Closed'[Date])
    ),
    FILTER(
        ALLSELECTED(Fact_Incoming_Closed),
        Fact_Incoming_Closed[Status]="Incoming"
    )
) 
VAR
    Closed_Tickets = CALCULATE(
    COUNT(Fact_Incoming_Closed[Request ID]),
    FILTER(
        ALLSELECTED(Fact_Incoming_Closed),
        Fact_Incoming_Closed[Date]<=MAX('Fact_Incoming_Closed'[Date])
    ),
    FILTER(
        ALLSELECTED(Fact_Incoming_Closed),
        Fact_Incoming_Closed[Status]="Closed"
    )
)
RETURN
    Incoming_Tickets-Closed_Tickets
The graph is working fine if I'm not filtering it. Problem occurs when I'm filtering based on the date :
 
As you can see, the measure "Backlog Tickets" is still "working" but result is not good anymore because it doesn't count the values before 2019-18 so the "Backlog Tickets" is starting at "-16" whilst it should start at "163" like it was on the graph unfiltered.
 
I know that the solution would be to add the previous backlog (Incoming - Closed before 2019-18) with the "Backlog Tickets" measure but I don't know how I can do that.
 
Many thanks for the help you could give me. 

I wish you an excellent day :) !
 
Yannick
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI yanx1990 ,

    You can consider to add all function to ignore all filters to get unfiltered records, then you can use condition filters on these records to get specific range backlog:

    Backlog Tickets =
VAR currDate =
    MAX ( 'Fact_Incoming_Closed'[Date] )
VAR Incoming_Tickets =
    CALCULATE (
        COUNT ( Fact_Incoming_Closed[Request ID] ),
        FILTER (
            ALL ( Fact_Incoming_Closed ),
            Fact_Incoming_Closed[Date] <= currDate
                && Fact_Incoming_Closed[Status] = "Incoming"
        )
    )
VAR Closed_Tickets =
    CALCULATE (
        COUNT ( Fact_Incoming_Closed[Request ID] ),
        FILTER (
            ALL ( Fact_Incoming_Closed ),
            Fact_Incoming_Closed[Date] <= currDate
                && Fact_Incoming_Closed[Status] = "Closed"
        )
    )
RETURN
    Incoming_Tickets - Closed_Tickets

    Regards,

    Xiaoxin Sheng

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi yanx1990 ,

    In my opinion, I think you need to use calendar date(not has relationship to current table) as axis and filter conditions to calculate. 

    Backlog Tickets =
VAR cDate =
    MAX ( Calendar[Date] )
VAR Incoming_Tickets =
    CALCULATE (
        COUNT ( Fact_Incoming_Closed[Request ID] ),
        FILTER (
            ALLSELECTED ( Fact_Incoming_Closed ),
            Fact_Incoming_Closed[Date] <= cDate
                && Fact_Incoming_Closed[Status] = "Incoming"
        )
    )
VAR Closed_Tickets =
    CALCULATE (
        COUNT ( Fact_Incoming_Closed[Request ID] ),
        FILTER (
            ALLSELECTED ( Fact_Incoming_Closed ),
            Fact_Incoming_Closed[Date] <= cDate
                && Fact_Incoming_Closed[Status] = "Closed"
        )
    )
RETURN
    Incoming_Tickets - Closed_Tickets

    When you use same table fields as filter, calculation result should been filtered by auto-exist filter if you not add all/allexcept to ignore specific filters.

    Understanding DAX Auto-Exist

    Regards,

    Xiaoxin Sheng

    • yanx1990's avatar
      yanx1990
      Frequent Visitor
      7 years ago

      Hi Anonymous ,

       

      Many thanks for your answer :). Unfortunately, this solution doesn't work. PBI returns an error saying : 

       

      However, I didn't think about using another table without relation. Maybe I will be able to make it working on another way.
      I'll try today and let you know ;)

       

      Thanks a lot !


      Yannick

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi yanx1990 ,

        Can you please share a pbix file with some sample file to test?

        Regards,

        Xiaoxin Sheng