Showing multiple date calculation values on one date axis

[pade]

I guess DAX is the way to solve my problems.

I have many tables with many date columns, but fot this example I limitied it to one table and two date columns like in TicketTable1 below. My task is to present a graph for how many tickets has been closed per day/month/year. But I also need to show how many tickets that are open and not closed.

TicketTable1(, 2, 3, 4, ...):

TicketNumber	OpenDate	CloseDate
1	2016-01-01	2016-02-01
2	2016-01-01	2016-01-02
3	2016-01-01	2016-01-03
4	2016-01-01

What I did was to first create a "DIM Date" table using CALENDARAUTO(). This will create a date dimension table over all my dates in all my tables (I guess). I then end up with a second "DIM Date" table as below

DIM Date:

Date

2016-01-01

2016-01-02

...

2016-12-31

My problem is that I don't understand how to relate data between the tables to be able to plot my diagram. As I understand, I can't relate all date-columns in all tables to the DIM Date table, so there must be an other way

I would then like to show a bar graph showing something like

Representing graph table

Date	Closed tickets sys 1	Still open tickets sys 1	Closed tickets sys 2	Still open tickets sys 2
2016-01-01	1	3	xxx	xxx
2016-01-02	1	2	xxx	xxx
2016-01-03	1	1	xxx	xxx
2016-01-04	0	1	xxx	xxx
2016-01-05	0	1	xxx	xxx

Anyone understanding my problem that could help me in the right direction?

[v-shex-msft · Accepted solution]

Hi @pade,

Based on your description, you want to get the subtotal and running total of your tables right?

If as I said, you can refer to below steps:

Data table.

 

Union these tables to one.

Formula:
TicketTable = UNION(ADDCOLUMNS(TicketTable1,"Parent","TicketTable1"),ADDCOLUMNS(TicketTable2,"Parent","TicketTable2"),ADDCOLUMNS(TicketTable3,"Parent","TicketTable3"),ADDCOLUMNS(TicketTable4,"Parent","TicketTable4"))

 Create the summary table.

Formula:

Table 2 = SELECTCOLUMNS(
DISTINCT(SELECTCOLUMNS('Table',"Date",[Date],
"Close1",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable1"),TicketTable[TicketNumber]),
"Open1",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=BLANK()&& TicketTable[OpenDate]<=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable1"),TicketTable[TicketNumber]),
"Close2",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable2"),TicketTable[TicketNumber]),
"Open2",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=BLANK()&& TicketTable[OpenDate]<=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable2"),TicketTable[TicketNumber]),
"Close3",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable3"),TicketTable[TicketNumber]),
"Open3",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=BLANK()&& TicketTable[OpenDate]<=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable3"),TicketTable[TicketNumber]),
"Close4",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable4"),TicketTable[TicketNumber]),
"Open4",COUNTX(FILTER(ALL(TicketTable),TicketTable[CloseDate]=BLANK()&& TicketTable[OpenDate]<=EARLIER('Table'[Date])&& TicketTable[Parent]="TicketTable4"),TicketTable[TicketNumber])
))
,"Date",[Date],"Closed tickets sys 1",if([Close1]>0,[Close1],0),"Still open tickets sys 1",if([Open1]>0,[Open1],0),"Closed tickets sys 2",if([Close2]>0,[Close2],0),"Still open tickets sys 2",if([Open2]>0,[Open2],0),"Closed tickets sys 3",if([Close3]>0,[Close3],0),"Still open tickets sys 3",if([Open3]>0,[Open3],0),"Closed tickets sys 4",if([Close4]>0,[Close4],0),"Still open tickets sys 4",if([Open4]>0,[Open4],0))

Result:
 

Regards,
Xiaoxin Sheng