In this article, I will demonstrate how to create a date table in SQL Server. This can be achieved by using the function generate_series which is available to you if you have SQL Server compatibility level 160 or higher. You can make use of this function in this case. However, many of you may not have that function yet. So I decided to take the looping approach.
In this case, I will use
While Loop — Loop dates (number)
DATEDIFF- to get the duration in days for the loop
DATEADD- add duration to a date
EOMONTH — to find the month-end date. You can use both positive and negative integers to move in either direction
FORMAT — format the date
DATEPART — to get the various date parts like a week, quarter, etc
DENSE_RANK- create a rank on year week
There are a few of these functions that appear to be very similar to power bi, but their syntax may be entirely different from power bi.
My Medium blog can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
There is a Power BI tutorial series that you can watch on my channel. Make sure you subscribe, like, and share it with your friends.