Forum Discussion
Sort months chronologically over running years
Hi, I'm very new to Power BI and DAX. I would very much appreciate the help. Thanks in advance.
I have attached below a sample of my primary table 'Tasks'.
The 'Completed' columns have the values 1 or 0 based on whether tasks have been performed successfully or unsuccessfully.
The 'Year' column has values 1,2 or 3 based on which year the task was performed. Year 1 is from 07/01/2020 - 06/30/2021. Year 2 is from 07/01/2021 - 06/30/2022. Year 3 is from 07/01/2022 - 06/30/2023.
The 'Date' column has the dates on which the task was performed. There can be multiple task performed on a same day and they are entered as separate rows.
I have created a Calender Table with
What I want to create is a clustered column graph with the sum of tasks performed successfully per month(y-axis), cumulative sum of tasks performed successfully(y-axis) and months of a 'Year' (x-axis). The 'Year' is chosen using a single-select slicer.
I have created a measure to find the sum of successful tasks:
This is for Year 1. As you can see, Jan21-Jun21 data is coming before Jul20-Dec20 data. Is there a way I can sort it such that Jan21-Jun21 data comes after Jul20-Dec20 and similarlyfor the other 2 Years. I watched a few videos which suggested to create a few additional columns in the Calender Table and sort the Cal Table[Date] column with these columns.
11 Replies
- johncolley
Solution Sage
Hi Anonymous ,
You need to use the 'Year-Month' column in the x-axis of the visual, looks like your using month of the Date in the image shown. You'll also need to make sure that 'Year-Month' is being sorted by the 'Year-Month Code'.
- AnonymousNot applicable
Hi johncolley,
I tried out your suggestion but the result is not as expected.
The cumulative sum is now showing the same value as the Completed Successful and the months beyond the year selected is also seen with no data. I have attached the download link for the file in the original post now.
- Ashish_Mathur
Super User
Hi,
Share the download link of your PBI file.
- AnonymousNot applicable
- Ashish_Mathur
Super User
That takes me to a sign-in page.