Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables:
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
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
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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |