Forum Discussion

paulfink's avatar
paulfink
Post Patron
6 years ago

*Challenge* DAX Formula

Hi Guys,

 

i have a really tough challenge for you, if you're up for it.

 

Here we go.

 

Entitlement formula i have already made:

Entitlement = if('Employee List'[Emp. Dates].[Date]>DATE(2018,01,01),if('Employee List'[Months of Service]<=6,0,if('Employee List'[Months of Service]<=24,5,(4*5))),if('Employee List'[Months of Service]<=6,5,if('Employee List'[Months of Service]<=24,(4*5),(12*5))))
 
The challenging part is to use this entitlement to determine if a person has gone into negative, which the total duration throughout the year will tell if they have gone over the entitlement, i need to see that negative data.
 
Now, using the entitlement, if a person has gone over the entitlement it will show in the table which i have done but it shows the overall amount, not the amount taken away by their entitlement.
 
What i need:
*Show when they have the entitlement (Date when they receive it, after the first 6 months. This will be their start dates + 6 months, i have used DateADD but this does not let me use it in a table)
*Excess - how many that has gone over their entitlement. Entitlement - Duration1 = Excess ( but this will need to be the same per person, if you simply put A - B then it will have different values for each row i need this to be the Entitlement - Overall Duration that will be the same per person.
*Duration1 - i have data for this which shows the duration is time taken and how many instances. This needs to be shown after they get an entitlement.
 
i have tried for a couple days now with no luck as you can guess.
 
if you have any questions ill answer them, this is sensitive content so i cannot show my work but can give a dummy sheet and what i am doing if needed.
 
Thank you.
Need Help

2 Replies