cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors