Best Way to Perform Like-for-Like Comparison in Power BI
Вставка
- Опубліковано 27 вер 2024
- Compare the Same Time Period from the Previous Year in Power BI / Time Intelligence in Power BI
In this video, we will learn how to perform a like-for-like comparison in Power BI. Often, when working with data in Power BI, you may need to compare data from the same time period in the previous year. #dax #dataanalysis #powerbi
⬇️Download the file - goodly.co.in/l...
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/l...
✔️ Master M Language in Power Query -
goodly.co.in/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l....
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co....
Corporate Training 👨🏫 - www.goodly.co....
Need my help on a Project 💻- www.goodly.co....
Download the file ⬇️ - goodly.co.in/l...
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI. Please browse around, you'll find a ton of interesting videos that I have created :) Cheers!
![[UA] Eternal Fire проти NAVI | EPL Season 20 Malta](http://i.ytimg.com/vi/chvlsDygrZM/mqdefault.jpg)
Download the file ⬇- goodly.co.in/like-for-like-comparison-Power-BI/
Also, check out my Hindi channel - www.youtube.com/@desigoodly/videos
Thank you, Chandeep! Quite interesting!
Actually, I use a similar approach in my calendar tables.
I create a column named When and populate it with -1, 0 and 1 for dates in the past, today and in the future.
This is much simpler than making all my measures take today() into consideration.
I am not sure, but I believe that this also alleviates the load on the Formula engine by moving this part of the queries to the Storage engine.
Another option is to add a column in the data set that represents last year sales. You can easily add this year and last year columns individually and perform any YOY calculations as needed.
Just as a follow up I've been trying for a like for like but where the pre year starts in say Feb, so for a like 4 like the current Jan has to be ignored, I've got this at the Month level,
using First non blank but still get a total for all the months, will persevere.
Thanks for good demonstrations, really easy to understand your explanations, thanks again for awesome videos.
Excellent !!! little fast on the demo ,yet superb..For me the OFFSET method works for any situation. In this example the current month of 2012 Aug ,if it is not completed month then there would be a diff with last year month cos in the example it is considering as completed month.
Thank you! Great tutorial!
Could you please make a Like-for - Like tutorial where to evaluate the days of the month (Current year vs Last year) from the day name perspective?
For instance, 18 May - for the current year is on Saturday and last year it was on Thursday.
On Saturday the stores are closed / only online shop is available; so, if I compare 18 May 2024 vs same day last year it means that I compare a weekend day with an working day.
What DAX measure should I use to solve this one?
According to the above example, the equivalent day for 18May 2023 is 16 May 2024 / Thursday vs Thursday.
Great content as always!
And remember Dax is simple but not easy 😊
great Video! I have one question though, I am using almost similar methodology as your second idea, but I am applying it directly in Power Query, which one is better and faster for calculation?
I see a flaw in your method (how bold I am, teacher 😅). I think that this way you cannot compare the evolution of sales as of August 31 from several years. I use this formula in PowerQuery but the equivalent version in dax is even easier.
= if (Date.Month([DATE])+Date.Day([DATE])/100)-
(Date.Month([SALES END DATE])+Date.Day([SALES END DATE])/100) < 0 then "True" else "False"
It can be modified with reference to the End of the Month with a Round.up which would be the equivalent of EOMONTH().
EOMONTH(MAX(Sales[Date])) returns a date format.
Then why subtracting 12 from it automatically goes back by 12 months and not 12 days or years?
by definition, EOMONTH will always shift the dates forward or backward by the MONTH unit. That's the way it's programmed
Great job!!!
Hi Goodly, can you help me with equivalent of ntile function on datetime column in power bi as i want to divide this date column distinctly in 4 equal groups.
Awesome!
Excelente
I prefer data modeling solution as this is easier to understand for outsiders, I guess.
I dont get the difference between a calculate in a calculate and a calculate with two filter conditions. Any help?
Video coming up soon
@@GoodlyChandeeplooking forward to it!
Also is very frustrating how I dont know when use filter inside calculate o crossjoin inside calculate.
Follow your guidelines is getting me more and more comfortable with dax ❤
Sir,need your guidance on one question, we r maintaining around 30 workspaces in our prod environment, for usage metrics z every time we r going each report and downloading manually, is there any way to automate instead of downloading?
Guess you can do that using Power Automate.
Thoda slow bola kar bhai, follow bhi tou karna hae.
Bhai, you have the option of controlling his speed in the settings of your video, use that ! I like his speed, but if at any time it appears fast, I slow it down from my settings.
Sir I used to see your videos. that is very useful specially for me . Sir I the job worth RS 10.5 LPA from HCL. You are amazing teacher for POWER BI excel and specially for logical concept . Once Again thank you so much
The first version of the measure [Sales LY Revised], the one built with the DAX approach, is clear and works correctly from January 2012 to August 2012 and in total in your examples.
I do not understand why in the months September 2012 to December 2012 it shows those values (40,195.59; 37,843, 74; 35,828.82; 33,607.53). How do I come up with those values?
So it would seem that the [Sales LY Revised] measure, following the DAX approach, does not work for the following months.. Don't you think so?
Thank you.
DAX should consider if dealing with complex data model, while filtering a calendar table could be better approached in small data sets.
Anyway thanks for sharing greate informative videos
The main question is wich approach is more effective on large amount of data
Hi, how do i compare this year same day with last year same day. For example, 10th Jan 2024 is Wednesday so I want to compare it with last year Wednesday which is on 11th Jan 2023.
Calculate([sales measure],dateadd(‘calendar’[date],-364,day))
Nice dude!