buble chart with date on X axes

[gunicotra]

Hi to all,

I have 5 products and I would like to plot on a scatter/buble chart the estimated time when each of them will hit the specific revenue target (i.e.: for product 1, the estimated date is 15/04/2021, for prod.2 the estimated date is 28/04/21 etc). The Y-Axis could for example be the rolling product revenue while, if necessary, the buble dimension could be the target.
I used a simple scatter chart, with a column "Products" and a measure "Estimated.Date.Target" which has the estimated dates of reaching the target, per products. 

However, as soon as I droped "Estimated.Date.Target" on "X Axis", I had the following error message:

I tried also using other scatter/buble visuals but I always had problem in showing what I have in mind!

Can anyone help me?

Thanks a lot in advance!

G

[amitchandak]

@gunicotra Scatter visual can only have one axis taken an unsummarized measure that is x-axis. Assuming y is already a number.

Can share the formula for measure Estimated.Date.Target

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[gunicotra]

Hai amitchandak!

sure:

Estimated.Date.Target = [MaxDate]+[Time.Target], where:

Time.Target = round(DIVIDE(([TargetFG]-[FG_YEAR]),[FG.Last.Week.])*7,0) --> gives the remaining days to reach the revenue target ("TargetFG"), starting from the last revenue update ("FG_YEAR"), at a FG.last.Week weekly revenue production rate.

MaxDate = calculate(MAX('TABLE'[DATA_IN]),'TABLE'[YEAR]=VALUES('TABLE'[YEAR])) --> gives the last day of the product revenue in the data table.

[v-yingjl · Accepted solution]

Hi @gunicotra ,

Seems like the Estimated.Date.Target is a measure, including measure [Time.Target] and measure [MaxDate].

In Scatter chart, measure could not be put in X-axis, the X-axis must be a meaningful column, not a measure.

You can try to convert all your measures to calculated columns and then put it in the X-axis.

In addition, I have noticed that you used VALUES() in the formula, if you have different year values in [Year] column, the visuals would still get error.

Supposing the max date is calculated for each year, you can create the column like this:

MaxDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Year]))

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gunicotra]

Hi! perfect thanks. Your solution worked for me 🙂