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

  • 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

      • yanx1990's avatar
        yanx1990
        Frequent Visitor
        7 years ago

        Hello,

         

        Anonymous Many thanks for your answer. Here's the PBIX and CSV sample data file.

         

        PBIX + CSV files

         

        Kind regards,

        Yannick