Plotting data on week numbers in a graph over years

[Anonymous]

Hi guys,

I'm working with a fact table and a date table to build my dashboard. My fact table has data by the week over several years. It contains a column with week numbers (actually it's year-week, so 201901, 201902, etc)

Is there any way to prevent Power BI filling in the "blanks" when making a graph based on week numbers?

Right now my slicer goes up to 201999, while there is only data up until 201952 and then continues with 202001.

Thanks in advance for your help!

EDIT: When I change the x-axis to categorical, it doesn't really solve my problem as I get a huge scrollbar.

[v-lid-msft]

Hi @Anonymous ,

We can try to create a calculated column using following Dax as the x-axis to meet your requirment:

DateColumn =
DATE ( ROUNDDOWN ( [WeekNr] / 100, 0 ), 1, 1 ) + [WeekNr]
    - ROUNDDOWN ( [WeekNr] / 100, 0 ) - 1

Best regards,

[Anonymous]

@v-lid-msftThank you for your insight. What would the result of that calculated column be? I'm getting a sequence of dates in the year 2563, so 19-9-2563, 20-9-2563, 21-9-2563, etc.

Using the formula:

DATE ( ROUNDDOWN ( Table[WeekNr] / 100 ; 0 ); 1; 1) + Table[WeekNr] - ROUNDDOWN ( Table[WeekNr] / 100; 0 ) - 1

[v-lid-msft]

Hi @Anonymous ,

Sorry for our incorrect DAX formula, we want to get the date of each weeknr, but after read your requirement again, it seems like you want to keep show week number in the axis, we are trying to find the solution and will update here if we find it.

D = DATE ( ROUNDDOWN ( 'Table'[WeekNr] / 100 , 0 ),1, 1) + 7*('Table'[WeekNr] - ROUNDDOWN ( 'Table'[WeekNr] / 100, 0 )*100 - 1)

Best regards,

[Anonymous]

@v-lid-msftYes, that's correct - I want to keep showing the weeknumbers (I do have the dates per week already, but my end-users will mostly work in weeks, not days)

Thank you, please let me know if you found a solution.

[Anonymous]

You could create a column of proper datetimes from your year and week number:

# %U assumes Monday as first day of the week. Use %W for Sundaycats['week_yr'] = pd.to_datetime(cats['year'].astype(str) + ' ' + cats['week'].astype(str) + ' 1',                                format='%Y %U %w')

Then you can do:

cats.plot.line(x='week_yr', y='transactions')

[VasTg]

@Anonymous 

Try with Categorical for x axis. 

[Anonymous]

@VasTgThank you for your reply. Please see my edit.