Forum Discussion
Showing multiple date calculation values on one date axis
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?
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
1 Reply
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