Plotting 2 dates from same table onto line chart

[graemejones]

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.

[Anonymous]

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

[graemejones]

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.

[Anonymous · Accepted solution]

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

[graemejones]

Thank you again for your help, I got this working and have now finished my report, which is the next problem. I uploaded my working report to the Power Bi service and it won't work online see below.
Working in Power Bi Desktop:

Working Report

After uploading it looks like this in the workspace:

Not Working Report

Below shows the tables in my Power Bi Desktop (right) and in the online service (left):

How can I fix this?

As always I really appreciate your help.

Graeme

[graemejones]

Hi,

I recreated the report from scratch and all is now working, thank you for all your help.

Graeme