cancel
Showing results for
Did you mean:
Helper I

## Calculate Weekly Total Based on Single Value and then Adjusted by Calculated Counts

I have a list of ticket items opened and closed within the last 6 weeks in two respective tables, as well as a current count of the number of active tickets.

The Opened and Closed data is accumulated into a clustered Bar Graph, showing the number of tickets opened and closed per week - what I am wanting to do is to calculate the ticket count total each week across those 6 weeks and have that displayed as a line graph item in that same graph.  The idea is that that weekly total would be calculated by taking the current total, then subtract the number opened and add the number closed last week to get last week's total, then add and subtract the previous week's totals  from that to get the previous total, etc etc.

Is such a calculation viable, or is there a better way to get this data displayed?

The tickets opened and closed tables look like this:

while the existing visualisation looks look this:

2 REPLIES 2
Super User IV

@PantherusNZ , the Issue you are facing not very clear. You can create a common date table and join it open date and close date with respective tables.

For Current ticket you need common ticket table

Current ticket = CALCULATE(COUNTx(FILTER(values(Ticket[Ticket],Min(Open[Start Date])<=max('Date'[Date]) && max(End[End Date])>max('Date'[Date]))),(Ticket[Ticket])))

Proud to be a Super User!

Helper I

The ultimate goal is to have a line graph showing the number of open tickets I had over the last 6 weeks, somehow calculated based on the number currently open, and the number of tickets opened and closed over that time period - I have the list of all the tickets, but I could also get the raw numbers of tickets opened closed per week if that makes it easier to calculate.

As for your advice on the table - I tried creating that table via DAX and got this error:

I assume I'm doing that wrong or in the wrong place?  I'm not familiar with some of those functions...

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors