Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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,
@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:
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,
@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.
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')
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |