March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello everyone,
I've just started using PowerBI (literally about half an hour ago), and the tutorial data that I'm using is showing DayOfWeek for 1-1-1999 as 4th day of the week, and the column also contains 0s as day of the week. Now 1-1-1999 was a Friday, so I'd really love it if the dataset could show me Friday as 5, Sunday as 7 and Monday as 1.
Is there a newbie-friendly way of doing this? Or should I come back to this after I've leartned R / M / Python / DAX (whichever applies)?
Solved! Go to Solution.
Hey,
unfortunately it's not that easy (w/o knowing M, DAX, R, or Python) to change this 🙂
Kidding, just a little DAX is necessary
I guess you have a separate table that represents the complete calendar.
The problem you are facing, is the following: the creator of the data that your are using created this calendar table, was thinking that Monday is best represented by 0. I also met a person decades ago that was thinking a similar thing, maybe it was the same person.
If you dare you could create your own (maybe your first calculated column by doing the following:
my Week starts on Mondays counting 1 = WEEKDAY('Calendar'[Date],2)
This will create a new column in your table, here is a screenshot that shows me entering the formula with the available options of the formula WEEKDAY
Please be aware that the learning track/material you are following may contain exercises that for some reason expect that Mondays count as 0.
Happy learning
Tom
= Table.AddColumn(PreviousStep, "Day of Week", each Date.DayOfWeek([Event Log Date])+1)
This code works for me
Jemma
Hey,
unfortunately it's not that easy (w/o knowing M, DAX, R, or Python) to change this 🙂
Kidding, just a little DAX is necessary
I guess you have a separate table that represents the complete calendar.
The problem you are facing, is the following: the creator of the data that your are using created this calendar table, was thinking that Monday is best represented by 0. I also met a person decades ago that was thinking a similar thing, maybe it was the same person.
If you dare you could create your own (maybe your first calculated column by doing the following:
my Week starts on Mondays counting 1 = WEEKDAY('Calendar'[Date],2)
This will create a new column in your table, here is a screenshot that shows me entering the formula with the available options of the formula WEEKDAY
Please be aware that the learning track/material you are following may contain exercises that for some reason expect that Mondays count as 0.
Happy learning
Tom
well, either the course-maker or Google are trolling me... (is this revenge for stepping outside of the course outline? we might never know)
6 July 1999 is showing up as a Sunday in the file, but google claims that it was a Tuesday.
Hmm,
I double checked, using Edge and Bing: July 6th 1999 is a Tuesday.
Here is proof:
I created a table Menu "Home" --> Enter Data
Entered the value: 1999-07-06
This format is recognized by Power BI Desktop as a datetime.
Adding the formula returns 2 - as expected.
Maybe it's somehow the revenge of your course instructor.
Keep safe 🙂
Tom
[sings] This makes me para-, para-, paranooooooid~
I'll now be double-checking everything in this course.
thanks for the help and the clear instructions @Anonymous and @TomMartens
Hey,
maybe you want to give this online course a try:
https://www.edx.org/course/analyzing-and-visualizing-data-with-power-bi
Cheers
Tom
Ooopps,
wasn't aware of that large Calendar table.
I'm sorry
Tom
Ha,
no I would never have taken the course by myself, if this would have been the case.
Maybe they are relatives, bad habits die slowly.
Cheers
Tom
Hey @Olia
I would add a calculated column with the following DAX code:
Column = IF( WEEKDAY(Table1[Date]) = 1, 7, WEEKDAY(Table1[Date]) - 1 )
This says: If it is a Sunday, provide a value of 7. Else, Provide the weekday number minus 1. This will give you a value of 1 for Monday, 2 for Tuesday, etc.
Hope this helps,
Parker
@Anonymous I fear that I might need a bit more explaining... what am I doing wrong?
I would close the Query Editor and go back to the main visualization view. You should see your table on the right. Right-click on your table and select "New column." Then paste in the code from above.
Thanks,
Parker
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
55 |