Thanks for tutorial, I had vision I was suppose to work with Power bi and excel and all of sudden I am doing this like I have done it before. I never even heard of this app until I meet someone at my new job that was using it, then that triggered my vision. I love power bi and to become data analyst. I have been working as IT support in a few warehouses over 20 years. I will watch all your videos.
Using 365 as a number of days value is giving me an error 😂 Beside that, isn't it easier using sameperiodlastyear? What is different from using: var filteredyearsales = SUM(Sales[lineValue]) var yearbeforesales = CALCULATE(SUM(Sales[lineValue]), SAMEPERIODLASTYEAR('Date Table'[date])) To get the values? Maybe it's late and I don't get it 😅
Hi there. I usually consider YoY growth only on the same period of both years. So, let's consider this scenario: 1. The previous year records start on 1st of July and the current year ends today, 23rd of October. 2. Total sales PY = 1.11M (01/07/23 to 31/12/2023) (calculated using SAMEPERIODLASYEAR), Total sales CY = 3.21M (01/01/24 to 23/10/24). Your 188.49% YoY growth in this case is incorrect, because the two periods are not the same. There is the period between 1/01 to 01/07 where there is no data in '23 but there is in '24, and there is the period between 24/10 and 31/12 where there is data in '23 but not in '24. And that's why I calculate the minimum date of 01/07 for both years and the maximum date of 23/10 for both years. In this case the total sales would look like Total sales PY = 1.05M Total sales CY = 1.16M (01/01/24 to 23/10/24), returning the 10.24% YoY growth. I hope this answers your question. Anyway, please let me know if you think there is something wrong with my logic, because at the end of the day I might be wrong myself :)
I have realised this whilst recording the next weeks video, because I was looking at 29th of February 2024 in my date table :). So I will change my calculation by using the date function. Anyway, I hope that on this Thursday I'll be able to publish a video that explains why I calculate my YoY% like that.
This somewhat seems complex. I was talking about the usage of new KPI card which has some great in-built features in it. But anyway thanks for the tutorial :)
First of all thank you very much for watching the video. Yes, there is a way of using the New Card visual, but that would involve using svg images and creating the measure for those would be even more complex, and I've tried to keep this tutorial accessible for the beginners as well. But, as we speak I am recording a video on how to create a KPI card with the sparkline to the right of the KPI, and maybe you will enjoy that one as well. It will be live around Thursday, next week.
Yes, definitely. I was so absorbed by using the negation of the negative that I missed the best (and most elegant) solution. Thank you for pointing it out!
This is very weird, because it is a standard line chart, and all I did was to o use a custom a title and subtitle. It should work with the most recent version of Power BI Desktop.
I like your approach! Looking at your code I have some doubts with correct leap year handling. Is there a reason why you avoid DAX time intelligence functions to do the date range calculations?
Thank you. I addopted this way of calculating the YoY % because my dataset does not include sales data for the whole year, and I've noticed that SAMEPERIODLASTYEAR does not consider that. I have noticed that when you have incomplete years and you want to return a year over year growth for the exact period of both years (anything other than that I don't consider as YoY), then the regular dax functions return wrong results. Yes, I could have used DATESBETWEEN to set the dates range, but (personally) I believe it does not make that much of a difference. I may very well be wrong on this :)
Excellent tutorial - thanks
You are welcome! I'm very happy that you enjoyed it!
Thanks for tutorial, I had vision I was suppose to work with Power bi and excel and all of sudden I am doing this like I have done it before. I never even heard of this app until I meet someone at my new job that was using it, then that triggered my vision. I love power bi and to become data analyst. I have been working as IT support in a few warehouses over 20 years. I will watch all your videos.
Thank you very much! I'm glad you enjoyed this tutorial, and I know this is the beginning of a very beautiful journey for you!
Really well done!
Thank you very much 😊.
I'm very happy that you enjoyed it.
excellent and very useful
I am glad you think so and I hope it will give you a starting point to create your own KPI cards.
Using 365 as a number of days value is giving me an error 😂 Beside that, isn't it easier using sameperiodlastyear?
What is different from using:
var filteredyearsales = SUM(Sales[lineValue])
var yearbeforesales = CALCULATE(SUM(Sales[lineValue]), SAMEPERIODLASTYEAR('Date Table'[date]))
To get the values? Maybe it's late and I don't get it 😅
Hi there.
I usually consider YoY growth only on the same period of both years.
So, let's consider this scenario:
1. The previous year records start on 1st of July and the current year ends today, 23rd of October.
2. Total sales PY = 1.11M (01/07/23 to 31/12/2023) (calculated using SAMEPERIODLASYEAR), Total sales CY = 3.21M (01/01/24 to 23/10/24).
Your 188.49% YoY growth in this case is incorrect, because the two periods are not the same. There is the period between 1/01 to 01/07 where there is no data in '23 but there is in '24, and there is the period between 24/10 and 31/12 where there is data in '23 but not in '24.
And that's why I calculate the minimum date of 01/07 for both years and the maximum date of 23/10 for both years. In this case the total sales would look like Total sales PY = 1.05M Total sales CY = 1.16M (01/01/24 to 23/10/24), returning the 10.24% YoY growth.
I hope this answers your question.
Anyway, please let me know if you think there is something wrong with my logic, because at the end of the day I might be wrong myself :)
@@MMS-PowerBI ah, I understand! Thanks for the explanation
@@MMS-PowerBI Hi, how can we account for years with 366 days in the measure? I’d really appreciate your feedback.
I have realised this whilst recording the next weeks video, because I was looking at 29th of February 2024 in my date table :).
So I will change my calculation by using the date function.
Anyway, I hope that on this Thursday I'll be able to publish a video that explains why I calculate my YoY% like that.
Brilliant thank you
You're very welcome!
I'm glad you enjoyed it.
This somewhat seems complex. I was talking about the usage of new KPI card which has some great in-built features in it. But anyway thanks for the tutorial :)
First of all thank you very much for watching the video.
Yes, there is a way of using the New Card visual, but that would involve using svg images and creating the measure for those would be even more complex, and I've tried to keep this tutorial accessible for the beginners as well.
But, as we speak I am recording a video on how to create a KPI card with the sparkline to the right of the KPI, and maybe you will enjoy that one as well. It will be live around Thursday, next week.
@@MMS-PowerBI Thank you so much Stellion 🧿
Where do I get the date Table. I got total sales by month on order date table. seems to work that way too anyways
In this video you can find the M code that I used:
th-cam.com/video/7qaIavKCaoI/w-d-xo.htmlsi=25EG71sHgiYBK43Y
It should be the final chapter.
14:13 or you just use ABS(YoY %) instead.
Yes, definitely. I was so absorbed by using the negation of the negative that I missed the best (and most elegant) solution.
Thank you for pointing it out!
How is DATE TABLE created?
I have a video on the channel where you can see how to get the exact same date table.
This is the link: th-cam.com/video/7qaIavKCaoI/w-d-xo.html
How to add this ?
What do you need to add and where?
@@MMS-PowerBI when I add this visual in my dashboard. It is not opening
This is very weird, because it is a standard line chart, and all I did was to o use a custom a title and subtitle. It should work with the most recent version of Power BI Desktop.
I like your approach! Looking at your code I have some doubts with correct leap year handling. Is there a reason why you avoid DAX time intelligence functions to do the date range calculations?
Thank you.
I addopted this way of calculating the YoY % because my dataset does not include sales data for the whole year, and I've noticed that SAMEPERIODLASTYEAR does not consider that. I have noticed that when you have incomplete years and you want to return a year over year growth for the exact period of both years (anything other than that I don't consider as YoY), then the regular dax functions return wrong results.
Yes, I could have used DATESBETWEEN to set the dates range, but (personally) I believe it does not make that much of a difference. I may very well be wrong on this :)