Power BI Week Visual Filtering

Question

Power BI novice here. I have multiple reports which require date filtering by week. I can sometimes get the data to display with my Week column using dates from a column in the same table.

I thought building a Week column based on the date column would result in an easy to use visual. The week column is calculated by:

WeekYear = IF(
           FORMAT(WEEKNUM(START.[Date],1)-1,"00"="00",
           "Wk53-" & YEAR(START.[Date])-1,
           "Wk" & FORMAT(WEEKNUM(START.[Date],1)-1,"00") & "-" & YEAR(START.[Date]))

This results in an x-axis displaying weeks in this format: Wk52-2019. If the underlying data of column STARTis in the proper datetime format, what could be the issue?

I noticed data on the visual which is not filtered for a date range display without issue. Trying to filter with DATESINPERIOD or other DAX date filters caused calculated measures to not display or break the model. I know a lot of references state having a separate calendar table is critical and I suppose I don't fully understand. Thanks in advance.

CR7SMS CR7SMS · Accepted Answer · 2020-01-13T19:39:17

If you are trying to create the week in date format, then you can use the following calculation:

Week = Table[Start] -  WEEKDAY(Table[Start],2)+1

This returns the Monday date of the week, if you want other days you can adjust the calculation accordingly.

If this is not what you are looking for, then you might have to clarify your requirements a bit more.

Power BI Week Visual Filtering

1 Answers