Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 

Cal Table = CALENDAR( MIN( Tasks[Date]) , MAX( Tasks[Date]) )
The Tasks[Date]  has a 'Many to 1' relationship in 'Both' directions with Cal Table[Date].

 

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:

Completed Successful= CALCULATE( COUNT( 'Tasks'[Completed]) , 'Tasks'[Completed] = 1 )+0
 
To find the cumulative sum for a particular 'Year', I created another measure:
Cumulative Completed = CALCULATE( SUM( Tasks[Completed]), DATESINPERIOD('Cal Table'[Date].Date] , ENDOFMONTH( 'Tasks'[Date]),-12,MONTH))
 
This is the result visual that I got:

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.

Year-Month = FORMAT('Cal Table'[Date], "YYYY MMM")
Year-Month Code = FORMAT('Cal Table'[Date], "YYYYMM")
 
But this has not produced the expected result. Is there another method I can follow?
 
Also is it possible to show the Cumulative Completed in months even if there are no tasks performed successfully and also the calender year along with the month name in the x-axis. That would also be a great help.
 

11 Replies