Date formatting

[tobiasmcbride]

Hi,

I have a spreadsheet which displays current and forecast data. I have a column which has the actual date a particular deal was closed by sales but also have data in there which is a forecast on when the deal is expected to be closed. The latter is done via quarters and the former in full dates like so:

Company	Expected deal close date
ABC	01/06/2018
DEF	Q4 2020
GHI	Q3 2020
JKL	Q1 2019

I am trying to get these to display nicely on a scatter chart whereby the expected deal close date is on the bottom, with the size of the scatter plot being the size of the deal itself which is another column on the table.

However I am struggling to get these dates to display nicely. Sales don't want to put specific dates on there for forecasted deals understandably and when trying to display on a scatter graph it sortes by Q1 first rather than quarter and year (i.e. I get Q3 2019 and Q3 2020 next to each other).

How would it be best to solve this so we can ideally maintain this quarterly perspective in the raw data but also get it to display nicely in a scatter graph or another table by date?

[tobiasmcbride]

I should also point out these are fiscal quarters with a year end of 31st March each year

[v-jayw-msft · Accepted solution]

Hi @tobiasmcbride ,

Create two formula like below then add them to visual and sort by quanter.

quanter = "Q"&FORMAT(EDATE('Table'[date],-3),"Q")

year = FORMAT(EDATE('Table'[date],-3),"YYYY")

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.