Skip to main content
cancel
Showing results for 
Search instead 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

Reply
HassanAli123
Helper I
Helper I

duration of a person's designation

Hi,

    I have two columns effective from and current position. i want to compute a colum of duration which will let us know the duration of a person designation. for example the duration of "assistant manager" will be 2 year and duration of "head" will be 8 years and 2 months. how can we find it through DAX.

Thnaks in advance

 

   Duration.PNG

1 ACCEPTED SOLUTION
8 REPLIES 8
v-henryk-mstf
Community Support
Community Support

Hi @HassanAli123 ,

 

Whether the advice given by @truptis @mh2587  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

actually i didn't try these solutions. I posted this question again and @amitchandak solved my problem. 

Hi @HassanAli123 ,

 

Can you provide a link to the solution here? Looking forward to your reply.


Best Regards,
Henry

 

mh2587
Super User
Super User

Duration =
VAR CurrentDate = CALCULATE ( MAX(Table[effective from] )
)
 

 

var LastDate = CALCULATE(
MAX(Table[effective from]),
 
Table[effective from] < CurrentDate )

 

VAR DD = DATEDIFF ( LastDate,CurrentDate, Year )
RETURN DD

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



truptis
Community Champion
Community Champion

Hi @HassanAli123 ,

You can use DATEDIFF function here. 

Eg - Result_for_years = DATEDIFF(effective_form, Today(), Year) 
(for first row, this will return no. of years between 01/01/2012 to today's date)
Result_for_months = DATEDIFF(effective_form, Today(), Month)
(for first row, this will return no. of months between 01/01/2012 to today's date)
Result_for_days = DATEDIFF(effective_form, Today(), Day)
(for first row, this will return no. of days between 01/01/2012 to today's date)

@HassanAli123 -> Please mark this as a solution if it helps you. Thank you.

Regards,

TruptiS

actually you did not get my point. the person is same in both designation. i want to calculate duration separately for assistant manager it should calculate till 2014 mean 2 years and 8 year 2 months for head roll

yes @HassanAli123 , it will give you the desired output. 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.