Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
I should also point out these are fiscal quarters with a year end of 31st March each year
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |