DATEDIF Function for calculation years, months, and days between dates

Question

Hello ,&nbsp;I have question how can I calculate in Power BI between start date and current date. My table1 calculates number of days since the start date but I am not able&nbsp; to work out how to create a new column with DATEDIF Function. For ilustration I attached the picture and table1 code. Number of days(JudgementDelayDays) to the current day are calculated in the blue circle. I was thinking to calculate it from these values in the blue circle... The second idea is to use start date(DefinitiveJugmentDay) from the date when the case was created to the current date(slicer)....&nbsp;Table1 =VAR MinJudgmentDates = &nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CecJudgement, &nbsp; &nbsp; &nbsp; &nbsp; "MinDefinitiveJudgmentDate", &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MIN(CecJudgement[DefinitiveJudgmentDate]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(CecJudgement, CecJudgement[CecCaseID] = EARLIER(CecJudgement[CecCaseID]))&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )VAR HasClones = &nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail,&nbsp; &nbsp; &nbsp; &nbsp; "HasClones", &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(CecRepetitiveCase, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CecRepetitiveCase[LeadingCecCaseId] = CecCaseDetail[CaseID]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &gt; 0, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )RETURN&nbsp; &nbsp; SELECTCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HasClones,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "JudgmentDelay1", &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [MinDefinitiveJudgmentDate], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOW(), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DAY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "JudgmentDelayInDays1", &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [MinDefinitiveJudgmentDate], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOW(), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DAY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED(CecLeadingCase[CecCaseID]) &lt;&gt; BLANK() &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail[StateOfProceedingID] = 3 &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED(CaseDetail[RegisteredNo]) &lt;&gt; "0"&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "CecCaseId", &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail[CecCaseId],&nbsp; &nbsp; &nbsp; &nbsp; "CaseId", &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail[CaseId],&nbsp; &nbsp; &nbsp; &nbsp; "HasClones", &nbsp; &nbsp; &nbsp; &nbsp; [HasClones],&nbsp; &nbsp; &nbsp; &nbsp; "DefinitiveJudgmentDate", &nbsp; &nbsp; &nbsp; &nbsp; [MinDefinitiveJudgmentDate],&nbsp; &nbsp; &nbsp; &nbsp; "DGIICaseName", &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail[DGIICaseName],&nbsp; &nbsp; &nbsp; &nbsp; "JudgmentDelay1", &nbsp; &nbsp; &nbsp; &nbsp; [JudgmentDelay1],&nbsp; &nbsp; &nbsp; &nbsp; "JudgmentDelayInDays", &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; [JudgmentDelayInDays1],&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; "RegisteredNo", &nbsp; &nbsp; &nbsp; &nbsp; RELATED(CaseDetail[RegisteredNo])&nbsp; &nbsp; )&nbsp;The result should look like this, in this format....below&nbsp;&nbsp;&nbsp;&nbsp;Any help most welcome.&nbsp;

anonymous · Accepted Answer

ok, I progrees a bit ....I added the code below to the new column in the table1&nbsp;Final2 =VAR _Years = FLOOR(YEARFRAC(today(),[MinDefinitiveJudgmentDate],4),1)VAR _Months = FLOOR(MOD(YEARFRAC(today(),[MinDefinitiveJudgmentDate],4),1) * 12.0,1)VAR _daysinMonth = DAY(EOMONTH(today(),0))VAR _days = IF(DAY(today())&lt;DAY([MinDefinitiveJudgmentDate]),DAY([MinDefinitiveJudgmentDate])-DAY(today()),DAY([MinDefinitiveJudgmentDate])+(_daysinMonth-DAY(today())))VAR _daysFinal = SWITCH(TRUE(),DAY(EOMONTH(today(),0)) = _days || DAY(EOMONTH([MinDefinitiveJudgmentDate],0)) = _days,0,_days)RETURN SWITCH(TRUE(),_Years&gt;0 &amp;&amp; _Months = 0 &amp;&amp; _daysFinal = 0,_Years &amp; " year(s)",_Years&gt;0 &amp;&amp; _Months&gt;0 &amp;&amp; _daysFinal = 0, _Years &amp; " year(s), " &amp; _Months &amp; " month(s) ",_Years&gt;0 &amp;&amp; _Months &gt;0 &amp;&amp; _daysFinal &gt;0, _Years &amp; " year(s), " &amp; _Months &amp; " month(s) " &amp; _daysFinal &amp; " day(s)",_Years&gt;0 &amp;&amp; _Months = 0 &amp;&amp; _daysFinal &gt;0, _Years &amp; " year(s), " &amp; _daysFinal &amp; " day(s)",_Months&gt;0 &amp;&amp; _daysFinal = 0, _Months &amp; " month(s) ",_Months&gt;0 &amp;&amp; _daysFinal &gt;0, _Months &amp; " month(s) " &amp; _daysFinal &amp; " day(s)",_daysFinal &amp; " day(s)")&nbsp;...and seems like some of them are correct and some of them are incorrect in the last column..&nbsp;&nbsp;

Forum Discussion

DATEDIF Function for calculation years, months, and days between dates

2 Replies

Related content

DATEDIF months calculation

calculate with max() function

SUM function doing wrongly calculation

calculate function

DATEDIF Function years, months, and days between dates