Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I have a measure that calculates number of days between two dates.
I'm trying to figure out a new measure that returns the number of years and months based on the number of days.
Ex: If the number of days measure is 400, the number of years and months should return "1 year & 1 month".
Thanks!
Solved! Go to Solution.
Hi,
You can try the below formula:
Year&Month = if( [Datedifference]<365, "Less than 1 year", INT([Datedifference]/365) & " years & " & ([Datedifference] - INT([Datedifference]/365)*365)/30 & "months")
The years will be shown without decimals, the months will have decimals, you can format those as well.
Regards,
IOana
@Anonymous wrote:Hi,
You can try the below formula:
Year&Month = if( [Datedifference]<365, "Less than 1 year", INT([Datedifference]/365) & " years & " & ([Datedifference] - INT([Datedifference]/365)*365)/30 & "months")
The years will be shown without decimals, the months will have decimals, you can format those as well.
Regards,
IOana
Great! I did some changes that worked out fine:
IF([Daysbetweenmeasure]<365; INT([Daysbetweenmeasure]/30) &" months"; INT([Daysbetweenmeasure]/365) &" years & " & FIXED(VALUE( ([Daysbetweenmeasure]- INT([Daysbetweenmeasure]/365) *365) /30); 0) & " months")
Hi,
You can try the below formula:
Year&Month = if( [Datedifference]<365, "Less than 1 year", INT([Datedifference]/365) & " years & " & ([Datedifference] - INT([Datedifference]/365)*365)/30 & "months")
The years will be shown without decimals, the months will have decimals, you can format those as well.
Regards,
IOana
@Anonymous wrote:Hi,
You can try the below formula:
Year&Month = if( [Datedifference]<365, "Less than 1 year", INT([Datedifference]/365) & " years & " & ([Datedifference] - INT([Datedifference]/365)*365)/30 & "months")
The years will be shown without decimals, the months will have decimals, you can format those as well.
Regards,
IOana
Great! I did some changes that worked out fine:
IF([Daysbetweenmeasure]<365; INT([Daysbetweenmeasure]/30) &" months"; INT([Daysbetweenmeasure]/365) &" years & " & FIXED(VALUE( ([Daysbetweenmeasure]- INT([Daysbetweenmeasure]/365) *365) /30); 0) & " months")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |