Forum Discussion

Naqash27's avatar
Naqash27
Regular Visitor
7 years ago

Opening Balance for Help Desk Tickets

Hi,   I have 2 tables: Help Desk List (the main table containing all fact data) Date Table (the date table which i made relationshi with my 1st table Created column) I want the Opening Balance...
Need Help
Naqash27's avatar
Naqash27
Regular Visitor
7 years ago

Here is the sample data:

Ticket ID |    Status     |     Created Date      |    Close Date

      1           Closed           01-02-2019             15-03-2019

      2           Open             03-02-2019                              

      3           Open             18-03-2019

      4           Closed           12-02-2019             15-02-2019

 

 

Expected Result from 1st March to 31st March would be:

 

Opening Balance    |      Tickets Created     |       Closed  Tickets       | Closing Balance

          2                                  1                                   1                               2

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Naqash27 ,

    You still not share any detail calculation logic about your formulas, please explain then more clearly.
    In addition, you can also try to use following measure formulas if they suitable for your requirement:

    Open balance =
CALCULATE (
    COUNT ( Table[Ticket ID] ),
    FILTER (
        ALL ( Table ),
        [Close Date] < MAX ( Date[Date] )
            || [Close Date] = BLANK ()
    )
)
    
Closed balance =
CALCULATE (
    COUNT ( Table[Ticket ID] ),
    FILTER ( ALL ( Table ), [Close Date] >= MAX ( Date[Date] ) )
)

Ticket Created =
CALCULATE (
    COUNT ( Table[Ticket ID] ),
    FILTER ( ALLSELECTED ( Table ), [Created Date] IN ALLSELECTED ( Date[Date] ) )
)
    
Closed Ticket =
CALCULATE (
    COUNT ( Table[Ticket ID] ),
    FILTER ( ALLSELECTED ( Table ), [Close Date] IN ALLSELECTED ( Date[Date] ) )
)

    Regards,

    Xiaoxin Sheng

    • Naqash27's avatar
      Naqash27
      Regular Visitor
      7 years ago

      It still did not work. The opening balance is not getting me the required results. Please clarify one thing, is Date[date] the Created date of the ticket or Date table?