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.
Great video appreciate your efforts but i have one doubt if you take year as 365 days hadcoded in that case during leapyear year consist of 366 days in that case one days sales data would be missing and would give incorrect sales sums
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 :)
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.
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!
Great video appreciate your efforts but i have one doubt if you take year as 365 days hadcoded in that case during leapyear year consist of 366 days in that case one days sales data would be missing and would give incorrect sales sums
You are correct, and I've changed the calculation for the YoY calculation video, using the DATE function and adding or subtracting one year.
@MMS-PowerBI could you please share the code?
Really well done!
Thank you very much 😊.
I'm very happy that you enjoyed it.
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:
ruclips.net/video/7qaIavKCaoI/видео.htmlsi=25EG71sHgiYBK43Y
It should be the final chapter.
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.
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 🧿
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: ruclips.net/video/7qaIavKCaoI/видео.html
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!
Brilliant thank you
You're very welcome!
I'm glad you enjoyed it.
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 :)
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.