Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Olia
Helper III
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
TomMartens
Super User
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

image.png

 

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

View solution in original post

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

TomMartens
Super User
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

image.png

 

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

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.

 

image.png

Hmm,

 

I double checked, using Edge and Bing: July 6th 1999 is a Tuesday.

 

Here is proof:

image.png

 

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

[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



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

That is exactly the course that I started with today. Trololol.

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

Haha! Yes, it's from Topic / Chapter 1. Is this person you mentioned by any chance the same person as course instructor?

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

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

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

 

image.png

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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