Changing DayOfWeek to show Monday as 1 and Sunday as 7

[Olia]

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

[Anonymous]

= Table.AddColumn(PreviousStep, "Day of Week", each Date.DayOfWeek([Event Log Date])+1)

This code works for me

Jemma

[TomMartens · Accepted 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:

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 

[Olia]

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.

[TomMartens]

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

[Olia]

[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

[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

[Olia]

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

[TomMartens]

Ooopps,

wasn't aware of that large Calendar table.

I'm sorry

Tom

[Olia]

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

[TomMartens]

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

[Olia]

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]

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

[Olia]

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

[Anonymous]

@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