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.
Requesting help from the awesome PBI Community with a probable combination of DAX formulas
I’ve been requested to calculate the number of carryover tickets month over month for our Service Desk Department. Carryover, in our case, is defined as counting the number of open tickets at the end of the month (regardless of when they were opened). Ticket are considered open when the Closed Date field is null.
Additionally, from this monthly cohort, I would like to identify how many tickets were opened 30 ago and 60 days ago. In a perfect world, I would like to add two lines to the graph below, one for # of tickets open more than 30 days, and another for the # of tickets opened 60 days ago.
Lastly, there are two different types of tickets – Service Requests and Incidents, which I would need counted separately.
Thank you in advance for your help!
Karen Payne
Mock-up data example:
Solved! Go to Solution.
Hi @Karen_Payne
I think this calculated table might be getting close.... Just replace the highlighted 'Table2' with your own table name.
New Table = SELECTCOLUMNS( FILTER( CROSSJOIN( ADDCOLUMNS( FILTER( CALENDAR("2017-01-01",TODAY()), day([Date])=1 ), "Reporting Month",var d = [Date]-1 RETURN DATE(YEAR(d),MONTH(d),1) ), 'Table2'), [Date] > [Create Date] && [Date] < IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date]) ) , "Reporting Month" , [Reporting Month] , "ID" , [ID] , "Ticket Type" , [Ticket Type] )
I get the following data from it
Thanks for this solution.
I have been checking this with my data, and I found that the following change was needed as items closed on the first of the following month were being excluded from the data.
[Date] <= IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date])
Instead of:
[Date] < IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date])
Thanks
Hi @Karen_Payne
I think this calculated table might be getting close.... Just replace the highlighted 'Table2' with your own table name.
New Table = SELECTCOLUMNS( FILTER( CROSSJOIN( ADDCOLUMNS( FILTER( CALENDAR("2017-01-01",TODAY()), day([Date])=1 ), "Reporting Month",var d = [Date]-1 RETURN DATE(YEAR(d),MONTH(d),1) ), 'Table2'), [Date] > [Create Date] && [Date] < IF([Closed Date] = BLANK(),"2099-01-01",[Closed Date]) ) , "Reporting Month" , [Reporting Month] , "ID" , [ID] , "Ticket Type" , [Ticket Type] )
I get the following data from it
How can this be done in a ssas tabular model ?Could'nt be done as a measure without having to create a table
Thank you. Worked like a charm.
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 |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |