Two dates in one table, related to a calendar table

[rckruege]

I'm working with 'ticket' data.. 

tickets have a 'created' and a 'closed' date.. I relate *:1 to a calendar table.

My goal is to plot a burnup/burndown with
1. cumulative sum of created ticktes.. by creation data
2. cumulative sum of closed tickets.. by closed date

How could this be accomplished in powerBI?  If I set my X axis to the calendar date.. I can only relate it to one of the dates in the primary table.

Do I have to create an artificial/duplicate table of the ticket table, and relate each table by 1 date column.. then ensure n ooverlap in my counts?

[vikrammankar]

Hi,

I have a similar but slightly different query.  I have a Power BI File with "Calendar" table.  There is one more table with name "Tbl_INC_SR_CR_Tickets_Query" with columns "Opened_MMM-YYYY", "Closed_MMM-YYYY" & "CarriedForward_MMM-YYYY".  Relationship in Calendar Table & "Tbl_INC_SR_CR_Tickets_Query" is on "Opened_MMM-YYYY".  I have a visual with 1 Slicer which has dates from "Calendar" table, & another table visual in which i want to show how many tickets are opened in the month which is selected from slicer, how many tickets are closed in the month which should also be selected from the same slicer, & also how many tickets are Carried Forward in the month which should also be selected from the same slicer.  So basically 1 single slicer should be able to show Tickets Opened, Closed & Carried Forward. I have created relationship between tables "Calendar"(Date Col) & "Tbl_INC_SR_CR_Tickets_Query" ("Opened Date").  Can anyone help please

[Ashish_Mathur]

Hi,

Share some data, describe the business context and show the expected result.

[Ashish_Mathur]

Hi,

Could you share some data and show the expected result.

[rckruege]

tickets:
created:            Closed      Status
----------      ----------      ------
01/01/1990      01/05/1990      closed
01/02/1990      01/06/1990      closed
01/03/1990      01/07/1990      closed
01/04/1990      01/08/1990      closed
01/05/1990                      open

What I'm trying to accomplish is 2 comulative counts on the same graph
1 showing the burnup of created tickets: a line increasing from 1 to 5 between 01/01 and 01/05
1 showing the burnup of closed tickets: a line increasing from 1 to 4 between 01/05 and 01/08

The problem I keep running into is that the X axis has 1 date field.

I'll graph a burnup of 'created' tickets:

All Created = 
CALCULATE(
  COUNTA('Tickets'[Created]), 
  FILTER(
     all('Tickets'[Created]),
     'Tickets'[Created] <= MAX('Tickets'[Created])
  )
)

and the challenge being a way to get the 'closed' line to show up based on close date... without being truncated:

[Ashish_Mathur]

Hi,

Perhaps i am getting confused with the burnup chart that you want.  Could you kindly show the result that you are expecting in a simple Table.  Once we can create a Table, we can also build a graph/chart.

Also, is there no Ticket Number column?  Is each row a different ticket?

[rckruege]

There are ticket #'s but I am just using countA() so not really needed for this example.  Each ticket is unique.

The goal would be something like this.  The problem is plotting both counts agains their dates, with a single X axis.  

[Ashish_Mathur]

Hi,

I just do not understand that chart.  If you can show your expected result in a simple Table, I may be able to help.

[rckruege]

The table is above.. the chart is the result.

I want to graph the line of cumulative count of all tickets based on their creation date, and concurrently all closed tickets based on their closed date.

So that would look something like:

created:           Closed      created
----------            ----------     ---------
01/01/1990      0               1
01/02/1990      0               2
01/03/1990      0               3
01/04/1990      0               4
01/05/1990      1               5
01/06/1990      2               5
01/07/1990      3               5
01/08/1990      4               4

[Ashish_Mathur · Accepted solution]

Hi,

The created cases for Aug should be 5 (not 4).  You may download my PBI solution file from here.

Hope this helps.

[Mehrnoosh]

Thank you Ashish,
How about if you need to count the number of tickets which are still open for each day(month in your example)?

[Ashish_Mathur]

That is a very dol post.  Share some data, describe the question and show the expected result.

[Mehrnoosh]

Thanks for your quick reply Ashish and sorry for the dol question. I was just referring to rckruege example and I though I can leverage it to my situation, but you are right, I better to provide my example as mine is more complex.

Imagine there is a plant which works 24 hours with 12 hours shifts staring at 6AM/PM. In this situation the “Production day” is different from calendar day and starts at 6 AM to 6 AM next day. This means 5 AM on 1/1/20 is still considered as 31/12/19. Now I have some equipment in this plant that they may have breakdowns and we want to see how many breakdowns we had per “Production day” and for how long.

Here is the source data:

Start Time                           End Time                             Time to repair(h)

1/1/20 8:00:00                   2/1/20   7:00:00                  23

1/1/20 13:00:00                1/1/20 17:00:00                   4

2/1/20 5:00:00                   3/1/20 8:00:00                    27

Here is the expected result in table form

Production Day (6 AM to 6 AM)  Number of Downtimes                Hours Down

1/1/20                                                      3                                                 27

2/1/20                                                      2                                                 25

3/1/20                                                      1                                                  2

But I would like to have a slicer to just filter base on the Production dates

[Ashish_Mathur]

Hi,

I do not have much experience in solving question based on Custom Calendars so i think someone else will help you.

[Mehrnoosh]

How about if we make it normal calendar? As I cannot resolve that one either.

In this case the result will be like this:

Date                     Number of Downtimes                  Hours Down

1/1/20                                   2                                                              20

2/1/20                                   2                                                              26

3/1/20                                   1                                                              8

[Ashish_Mathur]

Hi,

I cannot think of a simle way to solve this.  There will be many transformation steps to run.  May be someone else will help you with this.

[Mehrnoosh]

Thanks anyways. Hopefully someone can help.

[rckruege]

Huzzah!  I believe the thing I was missing is the ability to do 'userelationship' to use a non-active relationship.  That is great, thank you very much!