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?