highlight line chart period + event points

[Anonymous]

Hi, 

I finally have a 365 account so I'm able to ask for help haha. I have searched for solutions on the forum for the past few months but didnt find anything yet. I am making a dashboard for a discount on products. I get my data via directquery. I added a piece from a screenshot of my dashboard. My Manager wants to know how the products did in the period compared to the previous year (top left graph) and compared to the period before and after (bottom graph). I want to do 2 things. 

bottom graph: I want to highlight the current period shown in de top left graph. I do have a table with start dates and one with the end dates. I want it to show either as a different color in the table or as a bar behind it. As long as its clear where the discount period starts and ends. 

Top left graph: I want to show dates on which we have send mailings, posted on the socials, send mail (offline via post) so we can analyse where the peaks came from. I tried the Sparkline visualisation, but unfortunately I can only put points on the highest and lowest points, not what I want 😞 But I would like it to look like that. 

My data is as follows:

I have item numbers, that I use in a slicer. My item numbers are my key, they connect different tables to each other. 

I have posting dates and quantity. I have a seperate date table. 

Start dates and end dates

Event dates. 

Thanks in advance for your help!

[v-danhe-msft]

Hi @Anonymous,

Could you please offer a sample data to have a test if possible? And for your second issue, I could not figure what you want, could you please post your desired result?

Regards,

Daniel He

[Anonymous]

yes sorry.  I've made some example data in excel. The 'ledgers' are SQL tables from our nav Dynamics system. The others are just Excel tables. 

Below a quick photoshop example of how I would want the chart to look. I want to add points, like the red dot where I have send an email for example. And another where we put things on social, so if there is a dip or a peak after, we know why this is. If possible I would also want the legend to say: red is email, blue is social, yellow is post. But I think that is a stretch 😉 If it could show the dots on the given dates I would already be happy. 

[Anonymous]

I now see there was an update today. I read the blog and this sounds interesting for what I want for the dots 🙂 

Cross-highlight on a single point in line charts

Going to give it a try later today 🙂 

edit: nope, not what I wanted 😞

[Anonymous · Accepted solution]

I found a solution to display the period in the line chart by using some filtered measurs with the start and end date.  Maybe this helps someone else. 

Quantityafter = CALCULATE (
SUM ([quantity]);
FILTER('table';'table'[Posting Date]>=MIN(Dealsperiod[Enddate].[Date])))

Quantitybefore = CALCULATE (
SUM ([quantity]);
FILTER('table';'table'[Posting Date]<=MAX(Dealsperiodsheet2[Startdate].[Date])))

Quantitydeal = CALCULATE (

SUM ([quantity]);

FILTER('table';'table'[Posting Date]>=MIN('Dealsperiod'[Startdate])&& 'table'[Posting Date] <=MAX('Dealsperiodsheet2'[Enddate])))

And use all 3 as values with the posting date in the axis field. 

I had to put the start and end date in different tables and make new relationships to the date table so they were recognised as dates. 

Almost done, now I only need to have the events marked on the thing. Maybe I can do that if I make a line and column chart instead 🙂 And use the collumns as the markers. 

[v-danhe-msft]

Hi @Anonymous,

It's pleasant that your problem could be solved, could you please mark your reply as answer to let more people know?

Regards,

Daniel He

[Anonymous]

yes, but I unfortunately didnt find the solution to the other problem (to mark when we send mailings and stuff). 

[AnalystPower]

Hello@Anonymous,

Did you ever get this to show this way? I have 4 data/date points that I would like to show in a line chart.

---

[Anonymous]

@AnalystPower unfortunately not 😞

I'm getting a power BI course soon, so I can ask for help then 🙂 

[jochenj]

I also researching ways how to visualize stock-data as linechart with buy/sell/divident-events like this here:

my first test with a simple line chart brings me half the way:

Coloring the events by type should be possible with corresonding helper-measures,.. so far i found no way to dynamically add  horizontal lines at start of events (like in the first screenshoot). I will now reasearch if a R-Visual Plot will help here..