Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi,
I have a matrix - project hours by month.
I wish to sort the columns by fiscal year period and display the fiscal period year text.
I have sorted my table by the fiscal year period (key)
My matrix is still showing the incorrect sort order when I put the fiscal period year text in the columns. I would like it to sort chronologically. Can someone please help?
Thanks
Solved! Go to Solution.
Sorry, I should edit, I clicked on the "Fiscper Text" Column, and chose the wrong sort by column.
I have resolve my own issue
Hi,
You should ideally build a Calendar Table with Months and Year extracted using the following calculated column formulas
Year=Year(Calendar[Date])
Month=FORMAT(Calendar[Date],"mmmm")
There should be a relatiosnhip from the Date column of your base data Table to the Date column of your Calendar Table. Create another 2 column Table (Month_order Table) with Month and Order. Create a relatiosnhip from the Month column of the Calendar Table to the Month column of the Month_order Table. In the Calendar Table, use the RELATED() function to bring over the Order column from the Month_order Table (remane this new column to Order). Click on any month in the Calendar Table and go to Home > Sort By > Order.
In your visual, drag the Year and Month from the Calendar Table.
Thank you for the advice.
Why is it ideal or better to build a calendar table?
You are welcome. So that you can use DAX's Date and Time Intelligence functions such as PREVIOUSYEAR, SAMEPERIODLASTYEAR etc.
Sorry, I should edit, I clicked on the "Fiscper Text" Column, and chose the wrong sort by column.
I have resolve my own issue
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |