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