cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PantherusNZ
Helper I
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:

PantherusNZ_0-1623202435627.png

 while the existing visualisation looks look this:

PantherusNZ_1-1623202470454.png

 

2 REPLIES 2
amitchandak
Super User IV
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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for your advice.

 

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:

PantherusNZ_0-1623212063121.png

 

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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