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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NawilatiMahmoud
Frequent Visitor

How to write a Dax measure using the date table with mutable IF conditions

Dear All,

 

I need help in writing a Dax measure using the date table with mutable IF conditions as below:

 

I need two measures  :

 

1- (total days btween the date that I'll select in the silcer and the employee Hire Date  + the Extra days / 365 ) now we will have the years of employment for each in a measure or a coulmn, now in other measure or the same one if the years >=18 I need to return 100% if it's >=15 return 75% if >=12 return 50% if >=9 return 25% other return 0

 

if the employee hire date is 01-01-2010 and the slicer date is 01-01-2023 he will have 13 years then he will be 50%, if the slicer date is 01-01-2027 he will have 17 Years then he will be 75%

 

2- the other measure is the easy one if we complete the above, it will take the years of employment * the Salary * the above mesure % 100% or 75% or 50% or 25%.

 

thank you for your help in advance 

please find the below data to be used:

 

NameHire DateSalaryExtra Days
Name 16/1/2000 0:0030000
Name 26/1/2000 0:0025000
Name 36/1/2000 0:0040000
Name 412/13/2010 0:0025001065
Name 54/24/2011 0:0025000
Name 64/28/2011 0:0015000
Name 75/1/2011 0:0040000
Name 85/24/2011 0:0035001840
Name 96/7/2011 0:007000
Name 107/3/2011 0:008000
Name 117/10/2011 0:0012000
Name 128/1/2011 0:005001640
Name 139/11/2011 0:009500
Name 1410/4/2011 0:0018000
Name 155/4/2014 0:0014000
Name 166/10/2014 0:0016600
Name 175/8/2016 0:0058900
Name 185/22/2016 0:006500
Name 195/23/2016 0:008500
Name 206/1/2016 0:009540
Name 216/12/2016 0:0036500
Name 227/12/2016 0:008000
Name 237/17/2016 0:009000
2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Please find attached my PBI solution file.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

lbendlin_0-1686446729948.png

 

see attached

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.