logo

Month chart

Share on facebook
Share on twitter

Analyze days from two months on one line chart in Power BI

15 054 views | 28 Oct. 2020

Have you struggled to

Have you struggled to compare data from two different time periods in Power BI? Does the data just continue on and not overlap? Patrick shows you how you can easily overlay the data for comparison.

Download sample: https://guyinacu.be/twomonthssample

? Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

? Guy in a Cube courses: https://guyinacu.be/courses

*******************

LET'S CONNECT!

*******************

-- http://twitter.com/guyinacube

-- http://twitter.com/awsaxton

-- http://twitter.com/patrickdba

-- http://www.facebook.com/guyinacube

-- https://www.instagram.com/guyinacube/

-- https://guyinacube.com

***Gear***

? Check out my Tools page - https://guyinacube.com/tools/

#PowerBI #LineChart #GuyInACube

digjoy samanta

Hi Patrick,
Thanks for this!
Is it possible to have a Fiscal comparison/ weekly comparison STLY in similar fashion as suggested in this video?

Lewis Williams

Hi Patrick , Would you not just use the date hierarchy and delete year quarter and month to just give you day of month instead of writing a custom field

Azi Network

How to do for last 10 days ? Like analyise current days with last 10 lasts in line chart

Marcelo Chamarelli

The solution is simple, but I wouldn't recommend since the days on different months are not correlated. You don't want to compare sales from a friday on a month to a Wednesday on another. Having days of the week might be a better solution especially having the weeks. Not all will align, but the analysis should be better.

Lianne Wiersma

Hi Patrick, I was wondering if it possible to give readers of the report/dashboard the possibility to add comments in the visuals/tables shown in the PowerBI

Amey Raj

That's awesome will definitely try it....
Thanks for the video Patrick?

M G

brilliant

Manuel Basurto

Amazing!! Thanks!

Victor Guery

Has anyone been able to figure out how to establish a relationship between two tables where one has a date value and one has a start and end date? I've found this solution but isn't very intuitive https://community.powerbi.com/t5/Desktop/Create-relarionship-between-a-DATE-and-a-RANGE-OF-DATES/td-p/102719. In SQL this would be a simple join date between start and end date.

Alfred Lear

A teams group of ours involved in analytics went through power query uses and basics.
Your channel came up as a valued source, with several members singing your praises.
Just so you know, your work here is much appreciated ?

Marc Q

Hi, can you apply the same to the Excel charting (Power Chart/Power Query/Power Pivot)?

Paul-Sebastian Manole

Homework: extrapolate from this example and make year over year by month comparisons. Also change the filter on the left from radiobox to multiple items selection checkbox.

Driek Eijlders

Now one level up, how would you sync the weekdays?

Michael Roberts

I'm tend to look at last month to current month so created a measure with parallel period.

Pilot 3010

Not sure if this is the right forum for Scatter Charts, noted that the last scatter chart issue was circa 5 months ago, seizing the opportunity here. I had prepared a scatter chart Visual and it looks real good, unfortunately some data points overlay each other i.e. in my data source, 3 or 4 items have the same axis values BUT unique tool tips when I hover the data points, only the top layer's data is displayed. Is there a solution to this in Power BI? Appreciate your feedback. Thank you.

Devashish Jain

Hey, Yes I have done this before but sorry this is way to easy for you to specifically post a video.

Ketan Patil

very nice and simplified... keep rocking Patrick !!

Andy Parkerson

Great video and idea! It would be really helpful to line up day of week instead of day of month, so that you are comparing Mondays to Mondays and Thursdays to Thursdays. One way to accomplish this would be to create a column on your date table called modified_day,

VAR modified_day = day + WEEKDAY(DATE(year, month, 1)) - 1

This would augment the day of month by 0-6 depending on the day of the week of the first day of the month. So if the modified_day is 1, then the first fell on a Sunday. Now instead of putting Day along the Axis you put modified_day.

Mustafa Biviji

Patrick nice trick - Can this be extended to date-time (if you are comparing two different months data but is more granular as in minutes & seconds so needs to be on a continuous axis)?

Also what if there are gaps in the data for certain times (that is say no data for weekends ... can we have a break in the line)?

Pedro Pablo Inchicaque Paredes

Que genial.. salieron los subtítulos en español.. no es que los necesite XD pero igual que genial. Éxitos !!!!

Rafael Rangel

Good video Patrick. Question: if i have say 6 mesures and show 6 card. I want a tooltip that show a line chart of the measure. Need 6 tooltip. Is ther a way to do with one tooltip?

Sohel Islam

Patrik, how can i make a seach a matrix based on various conditions based on user input like or conditions on various columns be noted the table has no numerical values. All are text and date. It will like a search engine. My client want this as pro license. As they want to convert from talebu. Even power bi do not have run time sp calling on runtime. I am in a bad trouble. Do you have any solution...

Rohit Mishra

Hi Patrick, thanks for another video! i have one query can i pass the date slicer value in the Direct query as parameter .Please Help

Paul Cooper

Patrick, this is a common issue that I have solved before (not just in Power BI) using a date mapping field (e.g "last year date") which can be used to link in a copy of the data table. Too handle lining up of weekdays use a subtraction of 364 days rather than same date last year. (Also this stops issues with leap years.)

Paulo Taveira

Thank you for this video! It was a nice tip! Your videos do help a lot! What if you had a cube with facts related to a calendar dimenstion (with all days of the year) but you wanted to calculate a measure using data from a dimension table that contains only one value per month? How do you do that whithout having the data multiplied by the number of rows in the normal calendar dimension?

Andre Jaar

yeah, that's good... but there goes a challenge we took a long time here to find a solution and a leave to you hahhaha
the day 4 of Jan is not the same day 4 of Feb, because they are different days of the week... if you have only sells on day of the week, than this tecnique would not work
So...you should name the day of month based on the day of the week ( when you get this, than you could try to complicated a little bit more, adding holiday / "bridge" (if holiday is on thursday so friday is a "bridge" ) )
It's a fun challenge for those for work on sell's, we took 4 hours to do it :D

Andrey Zolotarev

0:22
activedating4.online

Christopher Phillips

0:51
beautiful-girls-here.online

Ivan

How can I drill down legends in a line chart?. My axis is date, my legends are in a hierarchy that I want to drill down, keeping the date axis

Rodrigo Parra Wong

Hi Guys, thanks for your tutorials! I’m trying to create a year over year line chart by day herarchy, but the x axis is fixed to categorical and I want continuos(to fit the size of the chart). Please your help

Luis Alejandro Rodríguez Campos

Hi Patrick, thanks for another amazing video!

Something I want to be able to create is a line chart that uses several metrics but also a legend... Using your example, I tried to create a chart showing one line for each month and an additional line for the average of all of them... so in order to override the month filter I did a new measure with an ALL() on it... however, I found that I can have one line for each metric or one line for each legend item, but I cannot have both... I tried creating a customized chart but I was not able to set it properly

Steve Williams

Is the sample pbix file available for download only supposed to include 12 days of data?

Month chart

Share on facebook
Share on twitter

MS Excel - Pivot Table and Chart for Yearly Monthly Summary

108 569 views | 22 Dec. 2013

ben cohen

Need help was'nt able to do it

M R

How can you do this type of trend if you have a lot of products (132 different)? Thanks!

Steve M

This guy is lost and does nothing but confuse the viewer

Micah

Added this to my youtube university playlist for excel. Great job. Thanks for making it!

Alonzo Thomas

Thanks man this is a life saver

tina:D

great video

Vishnendra Soonu

Helping video?

Month chart

Share on facebook
Share on twitter

Excel Pivot Chart with Slicers for Months to Show Values by Weekday Names

179 539 views | 13 Jul. 2017

This type of interactive

This type of interactive chart is great for impressive dashboards. Quickly learn how to create an Excel Pivot chart that's driven by pivot slicers.

Download the workbook here: http://www.xelplus.com/pivot-slicer-excel/

In this example I cover two topics:

1. How to get Weekday name from a given date in Excel

2. How to create a Pivot Chart controlled by a Slicer

In addition I show you how you can customize slicers in Excel to better match your report layout.

★ My Online Excel Courses ► https://courses.xelplus.com/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

https://www.xelplus.com/free-ebook/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg

Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR

Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161

Main Camera: https://amzn.to/3a5ldBs

Backup Camera: https://amzn.to/2FLiFho

Main Lens: https://amzn.to/39apgeD

Zoom Lens: https://amzn.to/3bd5pN7

Audio Recorder: https://amzn.to/2Uo5rLm

Microphone: https://amzn.to/2xYy9em

Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

hossein hosseinpoor

thanks

Eli G.

New sub! Wow! Blown away with the content. Such quality, beauty, and knowledge all jam packed in each video! (Thank you, YouTube!)

Muhammad Mudassir Hussain Siddique

Thanks its very useful to help me alot but I request u please increase your video quality its show me blur.

Tim Peng

Hey, how do you push the workday function down to the edge and fill them in a fraction of second?

José Carlos Conejo

Many thanks for your excellent videos and outstanding didactic abilities. There a before and after since I subscribed to your channel. I have one question on slicers. I am working on a dashboard for my boss with a slicer on months, but I need to limit the selection to one item, i.e., one month. I don’t want my boss to select more than one month at a time. Is it possible to limit selections in a slicer to one item at a time? Many thanks again for your videos...

Andreas Bodin

Great tutorial Leila!
I have a question. Is it possible having a pivot table with date and in the pivot chart under the date also have the weekday for that date?
So for 3/9 2016, I would like it in the pivot chart to show 3/9 and beneath the date, it should say Saturday.
I do not want to create an extra column with the weekday function.

I've tried to find a solution but get stuck. :)
Maybe it doesn't exist?

Evgenia M

Excel-lent video and this slicer format I was searching for a while finally it came to me! Thank you so much Leila!!

The Earth The way we knew it

Hi Liela, For Excel 2010 ,Just found out that to get Slicer for the Months - we can add a column containing the month name.TEXT(A2,"MMMM"). Then we do not have to go through the process of grouping as the Pivot Table field does not reflect the Month option like it is for your excel. Further the Slicer setting does not have the option of "Hide blank data".

sulaiman sait

Super mam you are explain so more update video for the excel sheet
I small douit mam you can explain daily for the payroll excel sheet

Yan Mike

Thank you so much for imparting the knowledge

iloveyoumadhuri

Hi Leila! I had difficulty getting the Months slicer into my pivot table for my Mac. At the 6:30 part, where I had to remove my original slicer, I could not get months by clicking on the dates in the Pivot Chart Fields like you. Is it possible to get help from you?

Shuska

Hi Leila. Great lesson! I find It very useful. I've prepared annual charts with Slicers for the sum of sales on different weekdays. Now I want to compare January (or any other month) weekday sum of sales (so I am not using averages but sums) between different years. However, I would like to add one more column to the chart and that is the sum of monthly sales. How can I do that?

Tristan Geoffroy

Thank you for the explanation on how customising slicer, .. very stylish as always with your tutorials, ...and a very sweet voice. thank you

Un Ingeniero

Oro, puro oro por estos rumbos.

S. Miller

You make it seem so easy and effortless. Thanks Do you have any lessoons with data to practice with?

Ravi Bopara

Firstly, many thanks to your tutorials on YouTube and on your website. Those helped me to boot my productivity levels in using Excel.

Here is my requirement:
1. I want to create a excel like interface with rows and columns in SharePoint with borders as my team members are familiar with excel.

2. When adding a new item in list, most of the columns need to be updated from auto-populated list for each column.

3. This list of options for each column will change on weekly basis and that is captured in an excel workbook which I want to connect to this SharePoint list.



If this can be achieved, please guide me.
If this requirement can be achieved with other set of office apps in a better way, please suggest.

Amir Ahmad

Awesome... ?

shuvankar choudhury

Mam how to convert numerical into word in excel

Jitendra Gupta

I learnt so many things like how to have multiple columns in slicers, how to make charts much more presentable..

Up4Excel

Great tips on formatting the slicer and getting months to show up on it.

Mohamed Naji

Thank you, is there a way to print out the chart with slicer? Always the slicer does not appear.

Dark Shinigami

How do you add the "Power BI" and "Power Pivot" tabs on Excel?

Patrice Airoldi

Very good explanations, now I know better how to create a pivot chart. Thanks for this very good video Leila !

Mahantesh Torgal

You are a wonderful and Excel Google for all of us...

mans0011

Thanks again for another great video. How would you force the chart to show empty weekdays? Don’t want to see Sunday then Tuesday, for example.

Reza kashi

excellent

Ranajoy Dutta

it wouldn't be a problem if i tell someone that you invented the Excel, as its totally believable :D you know the options so well

Tsiriniaina Rakotonirina

Dear Leila,
Thank you for your much appreciated training. I purchased all your 07 courses and finished the Basic Excel.
I'm on the Advanced Excel: Top Excel Tips & Formulas now. I am following step by step this 17. Tip 10- PivotTables: Calculated Fields, Slicers & Helpful Tricks and on the video *17:56*:

--->  I try to Center the 2016 and 2015 across the Selection. And the Headings below as well. It definitely works.
===> Unfortunately, when I update the filters, the formatting are gone every time.

I kept the Analyze>>Options>>"Layout Format" Preserve Cell formatting on update" Ticked but still nothing.
May you help me on this please?

Peter Ivarsson

Very interesting tutorial!,

Technical Afghan- پښتو

brilliant job thanks i am your previous subscriber and i also subscribe on this channel because your video give me many thinks . thanks

saro

Many Thanks!

Adlai Almeida

Hi Leila , please can you advise why in a data table you cannot combine and index match ?

mohit verma

Hi Leila,
Looking forward for your reply as i have a doubt after practicing. Slicer is able to control pivot and pivot chart, however data is not organised with the slicer. Is there a way that all three ( Data, Pivot Table, Pivot Chart) are controlled with single slicer. In your video as well it is same.

Frits van Bruggen

thanks for your examples, but how do you do that: date and automatically weekday, months, etc. Frits from Holland

ExcelFinanceTech

a beautiful video from a beautiful soul. Thanks a lot.

CHARLIE'S APKS

I HAVE 9 DIFFERENT PIVOT TABLES CHAR ON ONE SHEET BUT THE SLICER ONLY CAN CONTROL 2 WHY ?

Lutte NOOP

I am using Excel 2010. Please advise how to group by month?

Matthias Schumann

Hi Leila, I really enjoy your nice videos and have learned a lot. My current problem: I always have to present data and text in different Excel dashboard sheets. What I have is a big data base with different data. By slicer I select the needed Data and different pivots are presenting the correct results. In the dashboard I take over the pivot data with an lookup formula and they are there. Problem is, that the fixed cell wide is defined, the hight should be automatically extended and reduced by the lenghts of the text. I have no idea how that could be reached automatically by always choosing different data. If you have an idea here, or I did not found your right video... thanks for a hint.

Rick DeMarco

Great video, but when I create the Pivot Table, my row is still showing a date (1/1/1900, 1/2/1900, etc.) and not the day shown within the table. Any suggestions?

Yasir Arafat

Ms Access complete

Sheena Achyuthan

Awesome, thank you so much for showing the slicer function along with pivot chart.

Sanyukta Rajgolikar

hi leila, firstly thanks for the lucid lessons/ example.. secondly how to share excel workbook which contains slicers controlled charts? protected excel prevents slicer usage whereas unlocked sheet gives user scope of disarranging the layout etc Pls advise

Horváth Gábor

If I write the number 1 in a cell and then I go to the custom formatting and press DDDD - sunday appears. Okay. But why? Aaaand: interessingly, it doesn't depend on the operation system's settings.

Siddhbhushan Swami

suppose if we have two pivot tables with dropdown data validation in both of them. Now, for Table A, if I select a value from drop down, then I want a corresponding value to get selected from drop down of Table B. But the catch is, source of both the drop downs is same (i.e. a single column). Is it possible?

Vijay Sahal

I cnt add in pivot table month bcoz there are only date but while teaching class show there month but then I click date then show only date there are no month add..plz tell me how cn I add

Martiantouch

How do we sync slicers from different sources/sets of data? Thanks!

Trivikram Srinivas

Thank you so much

Puran Yash Kapoor

I am really blown away by the beauty of what you can create in excel. Keep it up !

KhOpHaE

Wow, I'm glad I subscribed, your channel is a gold mine, thank you!

Arif Gunawan

Very nice tutorial...very helpfull...how to analyze data with merge colom few subheader, please guide us on that

Erhan Er

I'm grateful for your tutorials. As an old-school guy, I have been learned a lot thanks to you.

Siddhant Salunkhe

The first thing Leila does after creating a table is to clear the color templates. She must be really hating those :D

Javel Palmer

Perfect! What's the easiest way to Import Data from the stock market Website to Excel?

With this Pivot style.

N Sanch01

You've helped me so much with charts and graphs. Thanks Leila!

asiflangha

You are superb. your examples are super easy to understand and likely to engage the viewer. Excellent job.

Paresh Thakare

After creating Pivot Table chart, I want to any perticular page within a same excel sheet which having 50 No.'s of pages, how can we make this? _pl reply

Luís Guillermo Pérez Mejía

Excelente aporte. Quedó suscrito. Cordiales saludos desde Medellín Colombia.

Miras Khabibulla

Your tutorials are amazing! Concise and straight to the point

kblyaq

Bad video quality

John Rice

Like for Leila as always!
I'm using pivots and slicers for a long time, but some moments was interesting for me.
So, thank you!

English Bob's Car Detailing

Hi Leila. I am trying to make a dashboard. It has multiple slicers controlling a pivot table. I want to stop the automatic sorting that happens to the slicers when selecting something. Do you have a solution for this. Great channel.

Edward Isaac

Hello leila,
kindly advise the way i can get your help with pivot table i have a problem that require your kind assistance.
Thank you,

TSAURAI KETIWA

You are just good in this.

Vee Bee

Very helpful. Great video.

chaz kaz

Have you got a in-depth video just on pivot tables?

Gerlie Fe Caacbay

Is it just me or the video is a little blurred? ??? I really wanted to watch this video.. :(

Akhil Reddy

Hello leila,i have a doubt on pivot please clarify..
For example,There is a data in sheet1 with dates,profits,loss etc.,A pivot table is inserted in the new sheet.Now my question is,there is a startdate & enddate in b4 & c4 respectively.If i change the dates the values in the pivot table should reflect only between those dates.How do we do that.Kindly help me with this

Ananth GJ

Hi Leila Gharani,

Ananth here writing from Bangalore, India. I am a big fan of you for the way you educate people in excel and other Microsoft products. I have learnt many many excel tricks from your channel.

I have a problem in excel. Could you please help me to resolve this issue.? if yes, please see my problem description below,

I have created a Slicer using Excel tables not using Pivot charts. I would like to fetch / print the information selected by user (in both slicers) into multiple cells in other tab for further processing.

Could you please help me to resolve this issue? Is it possible to do it?

Ananth GJ

Phone: +91 9902265005 / Whatsapp: 8861122333

Lathika Preethi

Leila, your lessons are amazing, simple and to the point!love it! One thing that I want to bring to your notice is that you have given a link to access and download the workbook you use in the course. The link really doesn't download it and I cannot find it on my PC as well. Can you pls check on that?

Adam Weaver

It's easier to use the TEXT function than it is to use the WEEKDAY function. Just type =TEXT(A2,"DDDD"), that will populate the fully spelled out day "TUESDAY", or type =TEXT(A2,"DDD") will give you the 3 letter abbreviation of the day, ie. "Tue"... That will save you from having to navigate the WEEKDAY function. This also works with months by typing "MMM" or "MMMM".

shafi khan

I regular learn from your videos, request you to make an end to end video on pivot tables from basics to extremely advanced (A-Z)

Nicky Banya

Hi Leila, your excel skills are excellent and very much effective in workforce management. This slicer is really nice. Can you please help with one click dashboard skills please.

Farith Ahamed

I have an excel table and I created a slicer for the table which does the filtering

Now I need to create a pivot chart where the slicer should work for both the excel table and pivot chart?

Basically I need to have one Slicer for Excel Table and Pivot Chart. How it can be made possible??

Archna Tripathi

Very Nice Video... Thank you Leila

Aaron Guild

Thanks!! This may be a unique request, but is there a way to maintain the row elements of a pivot, even if there is no data in the data table for that particular field? So, maybe in this example, if you have a filter by date (or date range), and wanted to show values by day of week, but the filtered range doesn't have Monday, but we want to maintain all 7 days in the pivot table even after I've filtered on a range that didn't include any Mondays. Does that make sense?

Dirik Willums

I love her way talking ?
Her language so easy to understand and explantion is clear ???

Farhan Khan

Excel is Magic...

J Robinson

Leila you never cease to amaze me!! Thank you for your easy to follow yet super informative vids!♥️

Hans Maes

Hi All!
First of all ... Great Video!
I will be using this a lot in the future.
Just 1 question. Is there a way to arange the weekdays in the chart from monday to sunday? No they are all mixed up with less reading association.

Many Thanks, Hans

cyberpunk fixer

If you do visio stuff that would be rad

Gilsimar Oliveira

Muito obrigado! Parabéns pelo vídeo!

Brian T

Hi Leila, thanks for this. What if we have different numbers of rows for each day (such as a line for each sale) and (here is the hard part) some days have zero rows. I can't figure out a way for days without a row to count as zero when the pivot table calculates the average.

Poppy & the Princess

Another excellent video, that provides clear instructions for the end-user. Thank you.

Ramlan Mansoor

To tell the truth , your explanation is 100% times better than my school teacher.
Thanks a lot this is really gonna help me in my exams tomorrow ☺?

Tube423

Actually, i dont know, why the people hitting dislike button.

Philip Dring

is there a way to have the slicer as a float so that it stays in same position as I scroll doen the table

Atia Abdelkader

Very nice video thank you very much I love ? it

sadavies2906

Love the videos Leila very easy to follow and results are great. In this example is it easy to include all week days in the pivot table and subsequent chart if there isn’t and data for the day in the table. Obviously as you use the slider the chart changes based on the data but would like to have 7 days showing all the time with blanks if nothing was on a Monday for example

Mahi Arts

Irviantanto Hadiprasetyo

It's very useful. Thanks, Leila

re bm

Hello, With the slicers of a pivot table, I can't keep the format (example wraps)
As soon as I click on another segment, there is no more wrapping and the box is unchecked ...

Don't Be Afraid

Hi Leila, can you please help me with my project and willing to pay. Hoping for your positive response. Thanks

Your fan

Dee

H Zaki

Hi, Just wondering if you can help. How can I link a Table and a Pivot Table to use one slicer to work on both based on the account name (for example) which appears n both tables. Thank you

Asep Channel

great explanation. easy to learn

Ajith Anandh

How to generate weekly report?
Week 1
Week 2 like that. Can you please explain

richstarx

I learn a lot from you because u are so beautiful

Mathieu LEGROS

Is it possible to select/unselect what buttons to display (or not) in the Slicers ? ?

GAUTIER Laurent

Just discover your channel a few days ago, yours videos are awesome. Simple and precise explanations to get what you want to do. Thank you very much for your teaching... and for us!

sandeep choudhary

Stellar Leila..!

Ili V.

You are so Awesome! Very easy to follow along...
Thank you so much!!!