How to change date format to be day of week?

[GilesWalker]

Hi everyone,

For some of our customers we have agreed numbers of units they will provide for us on each day, these units vary on product and also by day. For example, Product A is agreed to be given 16 times a day except Sundays, Product B 12 times except Monday and Thursday, etc.

Writing the formula in excel is easy, if(date="Sun", 0, 16). However I cant seem to do this in Power BI. The table I need to do this in has a date column but I cant seem to either create a column with a formula with the day in it, or get the formula to find the word (Sunday for example).

Hope you can help.

Thanks,

Giles

[Anonymous]

@GilesWalker It sounds like using a date table will go a long way in helping you out here. There are a bunch of ways to go about this, here are just a few depending on how you are implementing your model.

Azure Market Place

SQL

I almost always use a date dimension, it just simplifies so much in shaping the data the way I want to see it. You could then write your formula using the new dimension.

[GilesWalker · Accepted solution]

thansk for the response @Anonymous, the links provided me with some ideas. I ended up inserting a column with the following formula:

weekday(Date[date key],1). This will give a number for every date, Sunday being 1, Monday 2 etc. Then I could use an if formula to search off of the numbers.

Thanks,

Giles