Forum Discussion

ghouse_peer's avatar
ghouse_peer
Icon for Post Patron rankPost Patron
4 years ago

Count of Backlog data

Hi Team,     I have a table in which we have 4 columns named Ticket Number,Opened Date, Closed Date, State.   Ticket Number: Unique and may have different states based open Open and close. Opene...
Need Help
  • ghouse_peer's avatar
    ghouse_peer
    Icon for Post Patron rankPost Patron
    4 years ago

    Hi Sparta,

     

     Tried your calculation/DAX, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help

     

    For ex: lets say earlier to March month Backlog was 100 after all calculations.

    Now

                March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170

                April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265

    This Backlog count i need, PLease help

      • ghouse_peer's avatar
        ghouse_peer
        Icon for Post Patron rankPost Patron
        4 years ago

        Hi amitchandak 

         

        Thanks for the solution

         

        I tried your measure which you showed in your file. It returns only difference and count is high in case of my data.

         

        Please check this Detailed version of my question and scenario this would help you to generate solution.

         

        our expected output is :

         

         

        This line and clustered column chart represents count of state(open,Closed) w.r.t months. I need to get line graph(line Yaxis)of backlog.

         

        I have 2 tables for this.

        Table name: Data

        Ticket NumberOpened DateClosed DateState

         

        Table Name: Calender

         

        Relationship: 1 to Many [Date to Opened date: Unidirectional Active]

                              1 to Many[ Date to Closed Date: Unidirectional Inactive]

                              1 to many [ Date to State : Unidirectional Inactive]

        Calculation for Backlog [Line Y axis]: count of (Open-Closed+Previous Backlog) i,ePrevious backlog in the sense open-Closed of previous month and so on.

         

        Let us Assume Dec month Backlog was 250. in Jan 22 we got Open:300 Closed 210 so backlog will be (300-210)+250=340. This should be shown in line Yaxis in chart for Jan month.

         

        Kindly help with any other alternative to get the solution.

         

         

  • ghouse_peer's avatar
    ghouse_peer
    Icon for Post Patron rankPost Patron
    4 years ago

    Hi SpartaBI 

    Thanks for the input

     

     Tried your calculation/DAX, i am getting solution, but the count is wrong and difference of current Month(Open-closed)+ diff of previous month = Backlog count. This count i am unable to get it. Kindly help

     

    For ex: lets say earlier to March month Backlog was 100 after all calculations.

    Now

                March: Open =250; Closed=180; Diff=(250-180)=70; Backlog 70+100=170

                April: Open=410; Closed=315; Diff=(410-315)=95; Backlog 95+170=265

    This Backlog count i need, PLease help

    • SpartaBI's avatar
      SpartaBI
      Icon for Community Champion rankCommunity Champion
      4 years ago

      hey ghouse_peer, try this (I still missing some info about how you want to actually present the result, but I hope you this would cover it, I gave you two variations of the first VAR depending on what - I guess - are your options):

      Backlog =

--choose one version of the next VAR: 
-- in case you have a matrix with months as the filetr contest:
VAR _current_eom =
    EOMONTH( 'Table'[Date] ),0)
-----------------------------
-- in case you have a card to just show the current month based on TODAY() function with no other date related filter context:
VAR _current_eom =
    EOMONTH(TODAY(),0)
-------------------------------

VAR _all =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Ticket Number] ),
        'Table'[Open Date] <= _current_eom,
        REMOVEFILTERS('Table')
    )
VAR _resolved =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Ticket Number]] ),
       	'Table'[Closed Date] <= _current_eom && 'Table'[Closed Date] > 0,
        REMOVEFILTERS('Table')
    )
RETURN
    _all - _resolved



      • ghouse_peer's avatar
        ghouse_peer
        Icon for Post Patron rankPost Patron
        4 years ago

        HI SpartaBI  

        Thanks for the solution.

        our expected output is :

         

         

        This line and clustered column chart represents count of state(open,Closed) w.r.t months. I need to get line graph(line Yaxis)of backlog.

         

        I have 2 tables for this.

        Table name: Data

        Ticket NumberOpened DateClosed DateState

         

        Table Name: Calender

         

        Relationship: 1 to Many [Date to Opened date: Unidirectional Active]

                              1 to Many[ Date to Closed Date: Unidirectional Inactive]

                              1 to many [ Date to State : Unidirectional Inactive]

        Calculation for Backlog [Line Y axis]: count of (Open-Closed+Previous Backlog) i,ePrevious backlog in the sense open-Closed of previous month and so on.

         

        Let us Assume Dec month Backlog was 250. in Jan 22 we got Open:300 Closed 210 so backlog will be (300-210)+250=340. This should be shown in line Yaxis in chart for Jan month.

         

        I tried your measure and i was unable to create firs VAR out of two u have provided. It is not taking any of the fields from either of the table and was expecting a measure out of table to create VAR.

         

        Kindly help with any other alternative to get the solution.