How to calculate enrolment duration as a column with 'Present' as end date

[vikramjncasr]

Hi All,

NOOB ALERT, New to the forum and am trying to be mindful of the guidelines here but please bear in case of any issue.

I am trying to find the enrolment duration given the start date and end date but for the current students I have present listed as shown below:

I want to prepare a DAX column which will take the present date for those rows with 'present' as end date and then calculate the duration in MOnths or weeks..

I tried using Datediff and failed as it said cannot convert text type to date type.

[Thennarasu_R]

Hi !
@vikramjncasr 

Try This one you will get Your Out put

Measure= Var __Result = if(Enddate="Present",Date(Today),Enddate)
Return
Convert(__Result,Date)

 Thanks ,
Thennarasu 

[Idrissshatila]

Hello,

What we can do is add a new column to determine the end date to show us dates for all, so for the "present" will show us the value for every day by day.

New End Date = if( 'Table'[End Date] = "present",Today(), 'table'[End Date])

Now you can use datediff to calculate the difference between the start date and the new end date.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[vikramjncasr]

Hi @Idrissshatila 

Thanks for the response but when I try this I get the below error message probably to do with 'Present' being text format 

Is there a work around ?

[Idrissshatila · Accepted solution]

Hello @vikramjncasr ,

replace today() in your measure with this       format(TODAY(),"YYYY MM DD" )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin