Plotting two date fields on one date axis

[mccartanpat]

Hi Folks,

Hopefully someone can help me out here. Relatively new to PowerBI.

Am using the Salesforce case object where each record has an Open Date and Closed Date.

I want to plot the number of cases open vs closed over a period of months.

If I use either of the date fields as the axis then I will be limiting the other field by that date and as a result some records will be excluded.

What I think would work, if technically possible, would be to create a dynamic table that would automatically populate with the Case number, Date (open or closed depending on the source field), Flag.

Is it possible to insert a record with the Case number, Open Date, Flag of 'O' for all of the records with an open date populated?

Then insert a record with the Case number, Closed Date, Flag of 'C' for all of the records with the closed date populated?

So if the source looks like this: 

The format I am looking to to dynamically populate is this:

The result I want from PowerBI is similar to this:

This will allow me to filter by Open/Closed date rather than the source Open Date, Closed Date and allow me to show cases that closed in a certain month even if they were opened before that date filter (I have filtered here to only show Oct and Nov)

Kind Regards,

Patrick.

[v-chuncz-msft · Accepted solution]

@mccartanpat,

You may right click column [Case Number], then select Unpivot Other Columns in the Query Editor.

[mccartanpat]

@v-chuncz-msft,

Excellent stuff, (sorry for the delay in responding I got distracted by something else!).

Thanks a million for taking the time to help me. That worked a treat for what I was trying to to.

Kind Regards,

Patrick.