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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

      • yanx1990's avatar
        yanx1990
        Frequent Visitor
        7 years ago

        Hi Anonymous ,

         

        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 :smileyvery-happy:

         

        Yannick