cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Plotting data on week numbers in a graph over years

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?

image.png

 

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.

image.png

7 REPLIES 7
Highlighted
Super User I
Super User I

Re: Plotting data on week numbers in a graph over years

@Noeleke 

 

Try with Categorical for x axis. 

 

 

 

 

Connect on LinkedIn
Highlighted
Helper III
Helper III

Re: Plotting data on week numbers in a graph over years

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

Highlighted
Community Support
Community Support

Re: Plotting data on week numbers in a graph over years

Hi @Noeleke ,

 

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,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

Re: Plotting data on week numbers in a graph over years

@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
Highlighted
Community Support
Community Support

Re: Plotting data on week numbers in a graph over years

Hi @Noeleke ,

 

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)

 

21.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

Re: Plotting data on week numbers in a graph over years

@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.

Highlighted
Post Partisan
Post Partisan

Re: Plotting data on week numbers in a graph over years

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors