Scatter Chart - Date as Number

[Anonymous]

Hi Guys,

I have a problem with Scatter Chart. I want to plot the time in days it took to complete an item in Azure DevOps with the date it completed on, as well as a percentile line for 85th/50th percentiles.

Currently I'm having to use the completed date as a number, which means my chart ends up looking like so:

When it should actually look like this:

Data is structured like so: 

Any tips on how I could potentially restructure the data to get it working?

[Anonymous]

That's not technically true, you can add date to the x-axis however it then sums the data points...

When you change it to 'don't summarize' you then get an error...

 

[v-yulgu-msft]

Hi @Anonymous,

Currently, it doesn't support to add date field onto axis of scatter chart. We can only place numeric fields onto axis, for both X-axis and Y-axis.

Regards,

Yuliana Gu

[nirvana_moksh]

@Anonymous you can convert the last column in DAX using New-CompletedDateSK = Date(Left([Date],4),Right(left([Date],6),2),right([Date],2) You can refer to the below link for doing it in M https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

[Anonymous]

@nirvana_moksh I'm not sure how that solves my problem as I already have the CompletedDate column that has it in Date format? Apologies if I've misunderstood...

[nirvana_moksh]

@Anonymous - Apologies for misunderstanding your question and replying with something else, what I would do is use the numerical date column (20180930) on the X-Axis and that should fix the issue. I think by design for the scatter plot, placement of a categorical field on X-Axis vs numerical value on Y-Axis will require some sort of aggregation on Y-Axis. To circumvent this convert your X-Axis to numerical as well (20180930) and then change Y-Axis to Do Not Summarize and it should work.

[Anonymous]

@nirvana_moksh that's what I already have in the 1st picture in my OP 🙂

Is there any way I could create bins to reduce the white space? Or a grouping to go 20181101-20181130 / 20181201 - 20181231 / 20190101 - 20190131 etc...