cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

highlight line chart period + event points

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!

charts.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

 

period_chart.JPG

View solution in original post

9 REPLIES 9
v-danhe-msft
Microsoft
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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. 

 

excel_example_data.JPG

 

 

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. 

example chart.JPG

Anonymous
Not applicable

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
Not applicable

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. 

 

period_chart.JPG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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
Not applicable

@AnalystPower unfortunately not 😞

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

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

stock_visual_BuySellDivi.png

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

PBI_stock_visual_BuySellDivi.png

 

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..

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors