Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-lid-msft
Community Support
Community Support

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,

 

 

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.
Anonymous
Not applicable

@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

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)

 

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.
Anonymous
Not applicable

@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
Not applicable

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
Memorable Member
Memorable Member

@Anonymous 

 

Try with Categorical for x axis. 

 

 

 

 

Connect on LinkedIn
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.