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.
Hello,
I'm trying to make a report where the columns are week values calculated from a sql server datetime. The week values are calculated using the DAX calculation FORMAT(WEEKNUM([timestamp],2),"General Number"), and in edit queries, the datetime column is sorted from oldest to most current.
This has worked for the entries in 2018, but the column for week 10 from 2019 have wrapped around to the left, whereas week 9 of 2019 is in the correct sort order. And the columns are now in the order:
10, 44, 45, 46, 47, 48, 9
instead of:
44, 45, 46, 47, 48, 9, 10
Clicking the ... -> sort by in the table only allows me to sort by rows or by row values.
Any help would be appreciated,
Thanks.
Solved! Go to Solution.
You need to create a helper column in your table that would be the combination of year and week number, then tell your data to sort by that column. So it wouldn't be 9 & 10, but 201909 and 201910, which would then sort after 201848, 201849, etc.
Use the Sort By column feature. That is what my MMM-YY Sort field is doing for me.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou need to create a helper column in your table that would be the combination of year and week number, then tell your data to sort by that column. So it wouldn't be 9 & 10, but 201909 and 201910, which would then sort after 201848, 201849, etc.
Use the Sort By column feature. That is what my MMM-YY Sort field is doing for me.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |