Reply
Highlighted
Frequent Visitor
Posts: 5
Registered: ‎02-11-2019
Accepted Solution

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 Smiley Sad 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


Accepted Solutions
Frequent Visitor
Posts: 5
Registered: ‎02-11-2019

Re: highlight line chart period + event points

[ Edited ]

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 Smiley Happy And use the collumns as the markers. 

 

period_chart.JPG

View solution in original post


All Replies
Community Support Team
Posts: 2,061
Registered: ‎06-24-2018

Re: highlight line chart period + event points

Hi @Rienster,

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.
Frequent Visitor
Posts: 5
Registered: ‎02-11-2019

Re: highlight line chart period + event points

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 Smiley Wink If it could show the dots on the given dates I would already be happy. 

example chart.JPG

Frequent Visitor
Posts: 5
Registered: ‎02-11-2019

Re: highlight line chart period + event points

[ Edited ]

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

Cross-highlight on a single point in line charts

 

Going to give it a try later today Smiley Happy 

 

edit: nope, not what I wanted Smiley Sad

Frequent Visitor
Posts: 5
Registered: ‎02-11-2019

Re: highlight line chart period + event points

[ Edited ]

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 Smiley Happy And use the collumns as the markers. 

 

period_chart.JPG

Community Support Team
Posts: 2,061
Registered: ‎06-24-2018

Re: highlight line chart period + event points

Hi @Rienster,

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.
Frequent Visitor
Posts: 5
Registered: ‎02-11-2019

Re: highlight line chart period + event points

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