topic Re: Textual Display of Days (DATEDIFF) in DAX Commands and Tips

Textual Display of Days (DATEDIFF)

Kogika — Tue, 28 Apr 2020 14:09:59 GMT

Hi,

I`m using the DATEDIFF function and I`m getting the expected results in number of days.

What I`m not being able to do is to display the days in a more textual form. For example, If I have the number 398 days, I want to display " 1 year, 1 month and 3 days".

Ideally, I would like to use plurals/singular on Years Months and Days everytime it is applicable.

I've been struggling with that bu couldn't find any particular approach to make it work.

Tks!

Re: Textual Display of Days (DATEDIFF)

nandukrishnavs — Tue, 28 Apr 2020 14:44:20 GMT

Kogika

Try this example

DateText = var _fulldays= 35 //replace 35 with your measure var _year= QUOTIENT(_fulldays,365) var _month= QUOTIENT(MOD(_fulldays,365),30) var _day= MOD(MOD(_fulldays,365),30) var _yearText = IF(_year=1,"1 year",IF(_year>1,_year&" years",BLANK())) var _monthText= IF(_month=1,"1 month",IF(_month>1,_month&" months",BLANK())) var _dayText= IF(_day=1,"1 day",IF(_day>1,_day&" days",BLANK())) var _finalText= _yearText&" "&_monthText&" "&_dayText return _finalText

You may have to tweak the code a little bit.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: Textual Display of Days (DATEDIFF)

Kogika — Tue, 28 Apr 2020 19:38:14 GMT

Thanks nandukrishnavs , it worked perfectly! Very nice approach.

Regards