cancel
Showing results for 
Search instead for 
Did you mean: 
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


Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/
truptis
Super User
Super User

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 Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

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

Top Solution Authors