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

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