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.
I am trying to create a clustered bar chart showing count of closed and open tickets each month. That's the easy part. I want to add a line to the chart showing the cumulative difference between the amount of closed and open tickets. I've looked through the multiple posts on here concerning cumulative differences, but they all deal with values like sales and revenue whereas my data is categorical.
To be more clear with the cumulative part, I want to add up the amount of open tickets since the beginning of time in the data set. Then as time moves forward with each day that tickets are opened and closed, they are added to the cumulative total (open) or subtracted from the cumulative total (closed). It's complicated because it depends on that condition. Any help would be much appreciated.
Here's a screen of my data. In the Ticket_Status column, it either has Closed or Open.
Solved! Go to Solution.
Hi @Folio,
I'd like to suggest you use below formula to expand your original table, then you can direct use detail date range table to create visuals.
Expand Table = VAR _calendar = CALENDAR ( MINX ( Sheet1, [Created_Dt] ), MAXX ( Sheet1, [Resolved_Dt] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Sheet1, _calendar ), [Date] >= [Created_Dt] && [Date] <= [Resolved_Dt] ), "Ticket Id", [Ticket Id], "Ticket Status", [Ticket Status], "Date", [Date] )
Regards,
Xiaoxin Sheng
Hi @Folio,
I'd like to suggest you use below formula to expand your original table, then you can direct use detail date range table to create visuals.
Expand Table = VAR _calendar = CALENDAR ( MINX ( Sheet1, [Created_Dt] ), MAXX ( Sheet1, [Resolved_Dt] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Sheet1, _calendar ), [Date] >= [Created_Dt] && [Date] <= [Resolved_Dt] ), "Ticket Id", [Ticket Id], "Ticket Status", [Ticket Status], "Date", [Date] )
Regards,
Xiaoxin Sheng
Hi @Foliodoes something like this this solve your problem:
Measure =
CALCULATE(
countrows(table);
FILTER(
ALLSELECTED('table'[Created_dt]);
ISONORAFTER('table'[Created_dt]; 'table'[Created_dt]; DESC)
); 'table'Ticket_status" ="open"))
Hi,
In a simple Excel file, please show the result you are expecting.
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 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |