Reference Line Dynamically Based on Date

[jdballard30]

I have a clustered column chart with my date along the x-axis and sales amount on the y-axis. I would like to be able to put a vertical reference line on the date when a salesperson enrolled in a marketing program, so I can visually see the sales amounts before and after their enrollment in a program. Something like the image below.

I found this post, and tried it, but it doesn't seem to be working.

[v-juanli-msft]

Hi @jdballard30

The post suggest a workaround using line and stacked column chart and put [sales amount] into line values , but as line values can't show legends, the chart only contains one line, it doesn't meet your needs.

The only way is to Use Shapes to create a static vertical line and it is not dynamic as explained.

I come up with another workaround which may indicate the enrollment in a program.

Use line and stacked column chart,  Add [sales amount] into column values, measure into line values,

Measure = CALCULATE(SUM(Sheet4[sales]),FILTER(ALLEXCEPT(Sheet4,Sheet4[salesman]),
                   [enrollment]=MAX([DATE])))

Best Regards

Maggie

[jdballard30]

Thanks for the suggestion. Am I correct, that this won't work if there are no sales on the given date? So, if the enrollment date is 10/7, but there are no sales on that date, then it won't display the measure on the line?

Either way, it's given me a few ideas to try out.

[v-juanli-msft]

Hi @jdballard30

As tested, if the enrollment date is 10/7, but there are no sales on that date, then it still displays the measure on the line.

 

Best Regards

Maggie