cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naqash27 Frequent Visitor
Frequent Visitor

Opening Balance for Help Desk Tickets

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

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Opening Balance for Help Desk Tickets

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
Naqash27 Frequent Visitor
Frequent Visitor

Re: Opening Balance for Help Desk Tickets

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

 

Community Support Team
Community Support Team

Re: Opening Balance for Help Desk Tickets

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Naqash27 Frequent Visitor
Frequent Visitor

Re: Opening Balance for Help Desk Tickets

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?