cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

Changing DayOfWeek to show Monday as 1 and Sunday as 7

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)?

1 ACCEPTED SOLUTION
Super User

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:

1. Goto the Data window (the 2nd symbol on left vertical bar)
2. Mark the Calendar table that represents your calendar
3. Open the context menu of that table and choose "New Column"
4. Enter the following DAX Formula
`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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
14 REPLIES 14
Anonymous
Not applicable
`= Table.AddColumn(PreviousStep, "Day of Week", each Date.DayOfWeek([Event Log Date])+1)`

This code works for me

Jemma

Super User

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:

1. Goto the Data window (the 2nd symbol on left vertical bar)
2. Mark the Calendar table that represents your calendar
3. Open the context menu of that table and choose "New Column"
4. Enter the following DAX Formula
`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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper III

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.

Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper III

[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

Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper III
That is exactly the course that I started with today. Trololol.
Super User

Ooopps,

wasn't aware of that large Calendar table.

I'm sorry

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper III
Haha! Yes, it's from Topic / Chapter 1. Is this person you mentioned by any chance the same person as course instructor?
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper III
Maybe they are soulmates! ... This would make one heck of a plot for a romantic comedy. 2 people facing the horrors of the world, where everyone else thinks that the week start with Monday and assigning 1, trying to keep their sanity! ... Errmmmm...

Speaking of the first lecture, think that I will also need to learn to use Access Databases if I ever want to work in BI as a financial?
Anonymous
Not applicable

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

Helper III

@Anonymous  I fear that I might need a bit more explaining... what am I doing wrong?

Anonymous
Not applicable

@Olia

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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors