Annual Trend Graph Not Sorting Properly

[JK_PowerBINew]

Hi there,

I am creating a monthly absence report which requires the annual absence trends from this month last year (May 2021) to this month this year (May 2022). I have no date table as I receive the data from a different department which does not include this information.

1. I have two columns containing the Start Date of Absence and End Date of Absence for all absences from May 2021 to May 2022. However, data is missing for some of the End Date column as some absences remain open.

2. A column containing the Total Working Days Lost.

3. A column containing the absentee's role.

4. A column containing the absentee's domain.

4. A measure I created which counts the total occurrence of absence based on the role column which can then be broken down by domain. 

First of all, I am not sure which column to use as the x-axis on my graph.

Secondly, when I do select one of the columns the graph sorts January to December, rather than May to May. 

I can get it to work by extracting the month and year from one of the date columns and rather than using a hierarchy just select this value, however this reflects only the end date for that month.

I am keen to use best practice here and want to know how best to visualise these trends and get them to sort properly.

Thanks!

[JK_PowerBINew]

Both of these options sound viable and are really interesting - thank you! Although I have tried to make a date table to no avail. Can you guys possibly point me in the direction of some resources on this. I also have no experience with YTD measures - I am in the early stages of learning DAX.

[JK_PowerBINew]

Both of these options sound viable and are really interesting - thank you! Although I have tried to make a date table to no avail. Can you guys possibly point me in the direction of some resources on this. I also have no experience with YTD measures - I am in the early stages of learning DAX.

[Signore_Ands]

Hi @JK_PowerBINew - 
I agree with Amit in that I would always add in a Calendar dimension as it makes things a lot easier.
In the case of my own Lost time report, I'm just using a calendar months dimension and joining that to the month of the start of the absence.

The quesiton is - how are you expecting to see this data presented on the chart?

So for an absence that started on say 15 April 2022 and lasted until 15 May 2022 - do we:
show a lost time day for each day of that period or
show a 30 day loss against 15 April or
show a 30 day loss against 15 May?

Generally because lost time can extend across months and because you can have open items that are still accumulating days, I report on a single YTD figure for lost time in days - for the monthly amounts I report on the Count of incidents, counted against the start of the absence.

Maybe the right answer is a bubble chart wher the marker for the month of absence is sized accroding to the length of absence, but again based on the start date of the absence - in temrs of aocating it to a month.

[amitchandak]

@JK_PowerBINew , I would Advice to build a date table and use that. And create a formula like the one used Terminating employee

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...