Backlog issue when filter by Date

[yanx1990]

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

[v-shex-msft]

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]

Hi @v-shex-msft ,

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

[v-shex-msft]

Hi @yanx1990 ,

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

Regards,

Xiaoxin Sheng

[yanx1990]

Hello,

@v-shex-msft Many thanks for your answer. Here's the PBIX and CSV sample data file.

PBIX + CSV files

Kind regards,

Yannick

[v-shex-msft · Accepted solution]

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

[sreeni]

Hi Xiaoxin Sheng,

---

@v-shex-msft wrote:

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

---

---

@v-shex-msft wrote:

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

---

---

@v-shex-msft wrote:

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

---

[yanx1990]

Hi @v-shex-msft ,

It works like a charm !! Tested on my production database (the CSV was just a sample) and it's just what I expected to do.

Again many many thanks and have a wonderful day 

Yannick