cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JK_PowerBINew
Helper I
Helper I

Working out an Annual Leave Usage Traffic Light System

Thanks in advance!

 

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

Hi @JK_PowerBINew ,

 

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

https://docs.microsoft.com/en-us/dax/datediff-function-dax .

"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.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

Hi @JK_PowerBINew ,

 

Please refer below measure:

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.

vjaywmsft_0-1652772831744.png

vjaywmsft_1-1652772845891.png

 

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.

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!

Hi @JK_PowerBINew ,

 

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

https://docs.microsoft.com/en-us/dax/datediff-function-dax .

"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.

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

Thank you so much! This is great.

JK_PowerBINew
Helper I
Helper I

Hi 

 

Thanks so much for taking the time to reply.

 

Here is some sample data:

Sample Data for Power BI Help.png

And here is how I would like it to look:

Picture for Power BI Help.png

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. 

amitchandak
Super User
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"))

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors