Forum Discussion
Plotting 2 dates from same table onto line chart
Hi, I am trying to create a line graph that looks like this:
In my "tickets" table I have the below fields:
Can someone help as I cannot workout how to plot the Creation Date/Time and Close Date/Time with Dates/TImes on the Y Axis and Quantity of Tickets on the X Axis.
I have a unique identifier for the tickets called TICKETNO and assume a "Count of TICKETNO" would be in the Values secion of the Visual settings but I figure how to plot the to dates...
I am hoping that someone can help me with a guide.
HI graemejones ,
You not need to modify your database, my suggestion do these operations on data model side.(create a calculated table to expand original date range)
Steps:
1. Use unique fields and two date fields to create a calculate table with expand records.
2. Create relationship between raw table and calculate table based on above unique field.
3. Use new table date field as axis, raw table amount fields as value to create visuals.Regards,
Xiaoxin Sheng
5 Replies
HI graemejones ,
AFIAK, current power bi not support analysis data across multiple files.
For your scenario, you need to create a bridge table to expand all records between two fields and use unique id link to original table.(time demiton not suitable with this, expand time bridge table will generate huge amount of detail time records)
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
Hi Anonymous
Thank you for your help, I am however having trouble following this through and don't really seem to be getting anywhere.
I have tried to follow the linked example adjusting for the table and column names in my database but I'm having no luck. Is there any chance you could do me a step by step guide?
I do really appreciate all your help.
HI graemejones ,
You not need to modify your database, my suggestion do these operations on data model side.(create a calculated table to expand original date range)
Steps:
1. Use unique fields and two date fields to create a calculate table with expand records.
2. Create relationship between raw table and calculate table based on above unique field.
3. Use new table date field as axis, raw table amount fields as value to create visuals.Regards,
Xiaoxin Sheng
