Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JK_PowerBINew
Helper II
Helper II

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 II
Helper II

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors