Opening Balance for Help Desk Tickets

Question

Hi,&nbsp;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 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.&nbsp;Please help me with DAX query to get the required results. Thanks&nbsp;

anonymous · Answer

Hi&nbsp;Naqash27&nbsp;,
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] &lt; MAX ( Date[Date] )
            || [Close Date] = BLANK ()
    )
)
Closed balance =
CALCULATE (
    COUNT ( Table[Ticket ID] ),
    FILTER ( ALL ( Table ), [Close Date] &gt;= 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

anonymous · Answer

Hi&nbsp;Naqash27&nbsp;,
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 · Answer

Here is the sample data:Ticket ID |&nbsp; &nbsp; Status&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp;Created Date&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; Close Date&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Closed&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-02-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15-03-2019&nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Open&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03-02-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Open&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18-03-2019&nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Closed&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12-02-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15-02-2019&nbsp;&nbsp;Expected Result from 1st March to 31st March would be:&nbsp;Opening Balance&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; Tickets Created&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;Closed&nbsp; Tickets&nbsp; &nbsp; &nbsp; &nbsp;| Closing Balance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;

naqash27 · Answer

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?

Forum Discussion

Opening Balance for Help Desk Tickets

4 Replies

Related content

Calculate closing balance using opening balance and net cashflow

Creating Open Tickets / Backlog Evolution Measure & Graph Visual

Inventory and Stock balance calculation

Tickets, Open, Closed and backlog

Submit ticket