Forum Discussion
DAX DATEDIFF
I'm looking for a DAX query to return the difference between to dates in the format of x Years & y months
I have the following that nearby works
Years = DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), YEAR) & " Years & " & DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH) - DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), YEAR)*12 & " Months"
The problem is that it Rounds up the year so for 69 months I get 6 years &-3 months, instead of 5 years & 9 months,
If I round down the years it works for 69 months but for 16 ,months i get 0 Years and 16 mounths.
Hi Garbo62
Try to use this formula
YearMonths =
VAR Years = DDATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), YEAR)
VAR Months = DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], Today(),MONTH) - Years * 12
RETURN CONCATENATE(CONCATENATE(CONCATENATE(Years," Years, "),Months)," Months")
If this post helps, then please consider Accepting it as the solution to help the other members find it more quicklHi, Solved it
Age = INT(DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH)/12) & " Years & " & DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH) - INT(DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH)/12)*12 & " Months"
3 Replies
Hi Garbo62
Try to use this formula
YearMonths =
VAR Years = DDATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), YEAR)
VAR Months = DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], Today(),MONTH) - Years * 12
RETURN CONCATENATE(CONCATENATE(CONCATENATE(Years," Years, "),Months)," Months")
If this post helps, then please consider Accepting it as the solution to help the other members find it more quicklHi, Thanks.
Still returning 6 Years -3 months for 69 months not 5 years and 9 months
Alos minor spelling error for DDATEDIFF should be DATEDIFF
Hi, Solved it
Age = INT(DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH)/12) & " Years & " & DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH) - INT(DATEDIFF(cr6fb_gpe_a_aset[AAsetProcDate], TODAY(), MONTH)/12)*12 & " Months"