cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Working out an Annual Leave Usage Traffic Light System

I am trying to work out a set of parameters to flag those who are required to use up their annual leave before the calendar year runs out.

The annual leave calendar runs from October 1st to September 30th.

How would I take into consideration how many unused leave days a person has relative to the time of the annual leave year in order to flag them as green (do not need to be prompted to use annual leave) yellow (should use more annual leave but a prompt not yet required) and red (this person has to be prompted to use their annual leave before the year runs out) and what would these conditional parameters be?

Is there a DAX formula anyone could think of or does anyone even have tips regaridng the logic/calculation behind this to work out these conditions for a measure in order to create a traffic light system?

1 ACCEPTED SOLUTION
Community Support

Yes, you are correct. you could replace "Hour" to "Week" or other interval.

"rest_hours" is "remain_hours". This is a typo of mine.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
7 REPLIES 7
Community Support

``````Measure =
var remain_hours = if(FORMAT(TODAY(),"MMDD")<"1001",DATEDIFF(TODAY(),DATE(YEAR(TODAY()),10,1),HOUR),DATEDIFF(TODAY(),DATE(YEAR(TODAY())+1,10,1),HOUR))
return
SUM('Table'[unused])/rest_hours``````

Remain_hours means the remaining hours from today() to the end of calendar year(September 30th).

Then you could consider to add an conditional formatting to the visual.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper II

Hi there! This is amazing - thank you! Ideally, I would like the measure to use the unit of time weeks of the year. Is that possible? Sorry, I am new to DAX and only have a grasp of more simple measures! Would it just be a case of subsituting out HOUR for WEEK? Also what does rest_hours refer too? Thanks so much!

Community Support

Yes, you are correct. you could replace "Hour" to "Week" or other interval.

"rest_hours" is "remain_hours". This is a typo of mine.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Helper II

Hi Jay,

One last thing - why is the end calculation SUM('table'[unused holidays])/remaining_weeks? Would it not be the current week in the annual leave year/remaining weeks? If so, is there a DAX formula to work out the current week for a specified year period? thanks again

Helper II

Thank you so much! This is great.

Helper II

Hi

Thanks so much for taking the time to reply.

Here is some sample data:

And here is how I would like it to look:

We are looking to solve the problem of how to flag up those individuals who have not used enough of their annual leave relative to the time of the annual leave year as there is not a sophisticasted annual leave system in place. We want to create a traffic light system to make this easily identifiable for managers but are unsure of what the colour parameters would be and what the measure would be to work this out. I am new to Power BI DAX and know only the basics. We originally went with a percentage (higher the percentage the less holidays used) but this doesn't take into account the time element so we have had to manually work out each week how much annual leave they should have used by dividing the current week by the total number of weeks in the year and then changing colour formatting measure parameters. Each employee has different holiday entitlement.

Ideally looking for a more streamlined/intelligent and dependable way of doing this.

Super User

@JK_PowerBINew ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Usually, YTD calculation can reset every year, and that can be used, depends

YTD = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))