Displaying 'week commencing' date instead of week number

[PaulF0901]

Hello,

I was wondering if anyone might be able to help. 

I am currently showing activities by week number in a PBI report. I now need to modify the report so that the x axis shows the 'week commencing' date instead of the week number. Does anyone know how I might do that?

Here's my current model view:

Right now I count up how many activities (by activityID) exist for a user (user email) and then display it on a bar chart. I then use WEEKNUM on the X axis. 

Can anyone advise how to replace WEEKNUM with week commencing?

Many thanks

[bhanu_gautam · Accepted solution]

To display a blank bar for weeks with no activity in your Power BI chart, you need to ensure that all weeks are represented in your data, even if there is no activity for some weeks. This can be achieved by creating a separate date table that includes all the weeks you want to display and then creating a relationship between this date table and your activity data. Pull column from that table and Click on the X-axis field in the visualization pane.
Check the option Show items with no data.

[PaulF0901]

Ah, I've just figured out why the bar is not showing in the barchart. There are entries for the missing time period (May 13th) but those entries are filtered out by the filters on the visual 🙄

So I need to work out how on earth I can filter out rows but still have those rows show as blanks! 

[bhanu_gautam]

You can create a measure and filter out those values using filter in measure then there will be no requirment to use Filter on visual

[PaulF0901]

Thanks. You make it sound so easy 😄 

I'll go and figure out what a measure is. 

[bhanu_gautam]

@PaulF0901 , Can you accept this as solution as it helps in the original query you posted and if you need help in creating a measure you can post a new post for that

[PaulF0901]

Hi, this is wonderful, thank you. 

Just one more follow up question if I may. I have swapped WEEKNUM for WeekCommencing and it looks good. The only trouble is, I no longer have the empty columns (some weeks there is no activity). Any idea how to solve this? 
I have pasted a screenshot below. The second chart has less bars on it due to the face that there was no workspace creation activity in the week beginning the 13th. Any idea how I might just get it to display a blank bar? I had thought about checking 'Show items with no data' on the WeekCommencing field in the x axis but all that does is give me what you can see in the second screenshot. 

ss1

SS2

Thanks again for helping. 

[bhanu_gautam]

@PaulF0901 , For this you need to have weekcomencing column in your datetable

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2030, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"WeekNumber", WEEKNUM([Date]),
"WeekCommencing", [Date] - WEEKDAY([Date], 2) + 1
)

Currently it calculates monday and replace weekcommencing with weeknum in your visual