Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

Hello ,   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  to work out how to create ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    ok, I progrees a bit ....

    I added the code below to the new column in the table1

     

    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())<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>0 && _Months = 0 && _daysFinal = 0,_Years & " year(s)",
    _Years>0 && _Months>0 && _daysFinal = 0, _Years & " year(s), " & _Months & " month(s) ",
    _Years>0 && _Months >0 && _daysFinal >0, _Years & " year(s), " & _Months & " month(s) " & _daysFinal & " day(s)",
    _Years>0 && _Months = 0 && _daysFinal >0, _Years & " year(s), " & _daysFinal & " day(s)",
    _Months>0 && _daysFinal = 0, _Months & " month(s) ",
    _Months>0 && _daysFinal >0, _Months & " month(s) " & _daysFinal & " day(s)",
    _daysFinal & " day(s)"
    )

     

    ...and seems like some of them are correct and some of them are incorrect in the last column.. 

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    I sorted ther error was incorrect colulmn name but that code above is excellent.