Opening Balance for Help Desk Tickets

[Naqash27]

Hi,

I have 2 tables:

1. Help Desk List (the main table containing all fact data)
2. Date Table (the date table which i made relationshi with my 1st table Created column)

I want the Opening Balance of my helpdesk tickets tickets having the same logic which we usually practise in inventory systems. I used OPENINGBALANCEMONTH() function but i did not get any results. I have attached the screenshot of the visual i want to display Opening Balance under Open Tickets column. I am trying to create the measure.

Please help me with DAX query to get the required results. Thanks

[v-shex-msft]

Hi @Naqash27 ,

Please provide some sample data with expected result, it will be help for test and coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaxoin Sheng

[Naqash27]

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

[v-shex-msft]

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]

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?