In our models, the best way to deals with dates and DAX intelligence functions is to use a Calendar Table (Dimension). Good news, this table can be created in 2 clicks with Power Query (Transform Data) in Power BI!
Go transform data and right click on Queries to create a new Blank Query
Copy / Paste the following code
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable
Choose a start date and an EndDate, click InvokeThat’s it! You have a beautiful calendar table.Do not forget to mark this table as a data table
Credit: I found this code on internet many years ago and used it in the LazyDAX file. I think I changed it, but I can’t redirect and credit the one who created it. I think it can help you a lot, that’s why I’m sharing it with you 🙂
I created this Power BI desktop file (.PBIX) and share it with you.
Simple and clear data model
small data presentation
Quick explanation and examples
LazyDAX doesn’t include long and detailed explanation or comments. For each measures and function you have a link to microsoft.com, sqlbi.com or other external website.
I would like to teach you DAX from A to Z, but it’s not the best way to do it. (At least because I’m not one of the 2 italians! I would love to have their accent!! But I’m not!!). At least for me, I learned by doing mistake and try again and again.
IMO, to learn DAX, we need to practice! And that’s what I provide you, a place where you can add / modify your DAX measures.
You can still follow links and read the Microsoft documentation! That’s the reason why I changed my presentations to keep the same Canvas as Microsoft functions lists.
Microsoft Documentation
LazyDAX function list
Why?
Now, I use DAX everyday, but my journey was long and complex! How is it possible? Before my lovely job in BI, I was a “pure” developer. (C#, Java, PHP, SQL, VB, VBA, Perl, Pascal, C, Cobol, Fortran, Ruby, … I worked and learned a ton of different languages)
BUT DAX took me a lot of time to understand! I still learn new functions or logics. I hated and loved it a lot!
Now it’s the big love and I’m trying by many methods to explain it to different people. (It’s another level of complexity)
With this updated version of LazyDAX, I wanted to provide a simple and more clear file!
This is the Power BI file where you are the hero!! DAX is your unique weapon!!
I plan to add more functions and explanation in the coming months!
Or create your own Excel file by reading the text below (and copy / paste the vba code)
Why?
First it was a private joke, with this article it’s now a public joke.
Our client switched from Skype to Teams and it was a very cool improvement. My colleagues told me with a bit of fun: “Teams is cool, but you can’t change the time before appearing away!”. And it’s true, we can’t! Somebody said, “If you install a software who will click randomly on your screen, you will not appear away anymore”. Yes,.. but our software policy does not allow us to install anything on our computers.
It gave me an idea! Thanks to our very good old friend, Excel! People will say “Lazy is smart”, Arnaud is “funny” or any other reason. But I had fun with Excel and his perfect VBA editor.
(If you want your own excel sheet, the code is also available at the end of this post)
Thanks to Rafi for his 64 bits version!
How does it look like?
This is how it looks in Excel. You can start the timer by clicking on the start button or CTRL + l (like lazysnail) or start manually the macro named “DoNotSleep”.
To stop the timer, you have to press any key.
Do you have the VBA code?
If you are curious and want to build your own Excel, let’s copy the following VBA code:
Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As LongPtr, ByVal y As LongPtr) As LongPtr
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As LongPtr, ByVal dx As LongPtr, ByVal dy As LongPtr, ByVal cButtons As LongPtr, ByVal dwExtraInfo As LongPtr)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As LongPtr = &H8
Public Const MOUSEEVENTF_RIGHTUP As LongPtr = &H10
Sub DoNotSleepPlease()
Dim i As Integer
For i = 1 To 9999
'For Info, number of iteration
'Cells(1, 1) = i
If Cells(3, 5) = "" Then
SetCursorPos 200, 200 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
WaitPlease
SetCursorPos 300, 300 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
WaitPlease
SetCursorPos 400, 400 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
WaitPlease
SetCursorPos 500, 500 'x and y position
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
WaitPlease
Else
Exit For
End If
Next i
End Sub
Sub WaitPlease()
Dim sngWaitEnd As Single
sngWaitEnd = Timer + 5
Do
DoEvents
Cells(3, 3).Value = Timer
Loop Until Timer >= sngWaitEnd
End Sub
I am not a huge book reader, but I read a ton of different articles on the internet. They cover multiple topics like Power BI, SQL Server, DAX, Running, World news, High Tech review, Blogging, Self-improvement, and many other topics I want to cover at a specific moment. (Now, everything related to my house, design, …)
When I browse LinkedIn or Twitter, I find interesting articles, but sometimes I do not have time to read it. (Or maybe I am not in the mood to read them right now).
I also have a big trouble (and it seems that I am not the only one on earth). On a computer or a smartphone, my focus time is quite small. I like to switch between applications and topics, which decreases completely my learning curve. You know this little monkey in your head who tries to discourage you to finish something he thinks “too serious for him”.
Instapaper provides a clear and simple interface to collect, store and read all the web pages you like.
My workflow
On a smartphone: When I see an interesting article, I send it to my Instapaper application. This article will be synchronized across the Instapaper servers and all my different machines.
On PC: When I have a question, I go to my favorite search engine and pick up 5 or more articles and send them to Instapaper.
Later (or not), I open it on Instapaper and start to read. It’s often on my smartphone when I have time. (In the public transport, sofa, waiting time, …)
5 features I like:
Reader mode: Instapaper provide a better interface for you to read the article. Better format, font size, … A dark mode is also available, which is super cool!
Highlight: I don’t use it after all, but I feel more concentrated when I Highlight important text and be happy to know that I can found it back if needed.
Offline mode: Ok, now we always have an internet connection. We don’t also have a roaming fee in the EU. But it’s cool to be able to read your articles and disable your internet connection and be more focused. (Hey Instapaper dev team, if you read this post: A Pomodoro timer inside Instapaper could be a huge improvement!)
Archive: I can archive the articles I liked (or I read) and find it back later. No more sorry when at the coffee corner we said. “I read an interesting article, I would like to share with you, but I don’t find it back”.
Export a list of your links in an HTML format (easy to share with you colleagues or friends)
And I forgot to mention: THE KILLING FEATURE!! (At least for me)
Which is for me the best thing ever and the reason why I switch to the “Pro” / “Pay” version.
A nice Kindle integration!!
Instapaper allows me to send my unread articles to my Kindle. The configuration is pretty easy, and the result is very nice!
I receive the file through my kindle email address and can read all the internet articles with the beautiful E Ink screen.
On my kindle, all the articles are grouped in “one book” and each article is separated by chapter, which allows me to switch easily between articles. All Kindle features are available, you can highlight the text you want to save, search for a translation, or a Wikipedia explanation.
Reading with a kindle, I think there is no better choice to keep the focus on this task and it gives a different atmosphere to web articles.
I wanted to share with you because I simply think it’s super nice!!!
(BTW I’m not paid to promote their tool or the Amazon Kindle 🙂 )
LazyDAX helps you to understand DAX with concretes and simple examples. You can change the existing measures or create yours. LazyDAX is also an easy dataset for your tests and presentations.
Why LazyDAX?
In my DAX journey, I met two kinds of peoples.
Business Analyst (sometimes also Excel Users) who had very good abilities to learn DAX.
And people like me, with a slow learning curve with DAX. (Unfortunately, I’m not so bad with SQL, C#, and a ton of different languages. Yes I’m a developer :))
During our DAX learning, Mathieu Ricour and I built the first version of Lazy DAX. It was a place for us to keep and save our code around a simple and concrete example.
After many months, I’m still using this file on a quasi daily basis. And I’m convinced, it can also help you.
In this second version, I’m sharing with you a list of useful measures and a reusable dataset.
