Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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 Employee
Microsoft Employee

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.