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

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.


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

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


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #LineChart #GuyInACube

[alfredlear]

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 👍

[pcooper]

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

[andyparkerson]

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.

[andrejaar]

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

[EyiBillion]

Yoy! My name is Patrick from Guy in a cube. 😂. Keep the good work going

[ketanpatil]

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

[driekeijlders]

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

[mustafabiviji]

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)?

[analyzethis]

how is the calendar table related to other tables?

[stijnproot]

Well this movie made allot very clear for me, Thanks for that!

But I do have 1 question can I select multiple years and select the month for example January to than compare the months January of the different year?
Now a have the issue that they are combined into 1 line for January.

[rishabhbhagat]

I want to visualise filter as buttons as shown in this video. Is there is any video for this so that I create similar to this?

[PaulSebastianM]

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.

[ameyraj]

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

[jameslaine]

Neat, but can you further align by the day? for example, regardless of the specific day number in the month, I want the visual lines to start at different offsets, so that all saturdays, sundays, mondays, etc. align vertically on the chart.

[chramakanth]

Hi, its a great video. can you show how to overlay 15 months of data for current year (Jan 2023 thru Mar 2024) vs previous year (Jan 2022 thru Mar 2023) with week numbers on x axis with two line graphs with same starting point?

[SandeepYadav-vmhd]

Q:
Thanks for the great information.

I just want to know if my date table start from Apr'23 till July but I want to show Trend line chart only for the month of July from 1st July till 31 . What will be approch. And next month in August line change start from 1 aug to given date in calendar table.

[jigneshsvit]

Hey, this is good however if we want to compare a particular month of one year with another year ? a year with all months of a particular year plotted in comparison to another year ? so basically a custom date comparision, if you could guide on this or if you have already done something similar in any of your earlier video, kindly pls provide the link. Thanks in Advance !!

[stevewilliams]

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

[mchamarelli]

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.

[andrewwatsek]

Nice video Patrick! Is it easy create a line graph with 3 lines that combines running totals over current year and the last two years in one line graph. so 3 lines, 1 for each year. I've been struggling with this.