DateIF Help

[AliceInChains]

Hi There,

I have worked out the average between two date columns (i.e. Average length of tenancy for x records based on Todays Date) using the follwoing base calculation in DAX:

nDays = if( [Tenancy Start Date]<>Blank(), (DATEDIFF( [Tenancy Start Date], TODAY(),DAY)),Blank())
/* Used to calculate the days difference between two dates … this is required for aggregation summaries */

I then take the average of nDays

mDays = average(ndays) 

I want to return the results as XX Years YY Months and ZZ Days

I can do thsi in Excel using 

=DATEDIF(0,D6,"y")&" Years "&DATEDIF(0,D6,"ym")&" Months "&DATEDIF(0,D6,"md")&" days"

Example:

DOB	Today	Datedif
14/12/1961	8/12/2017	20448
15/12/1961	8/12/2017	20447
16/12/1971	8/12/2017	16794
17/12/1980	8/12/2017	13505
	Average	17798.5	48 Years 8 Months 22 days

It appears that DAX does not support "ym" and "md" .... If I use only "Month" and "Day" i get the wrong result.

DAX Version

MeanDays =
Concatenate (DATEDIFF(0,(AVERAGE([nDays])),YEAR) & " Years ", CONCATENATE(DATEDIFF(0,(AVERAGE([nDays])),MONTH) & " Months ", DATEDIFF(0,(AVERAGE([nDays])),DAY) & " Days "))

Can anyone help?

[AliceInChains · Accepted solution]

Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers

[parry2k]

here is something you can do, just a guideline, you can tweak as you see fit,

add new measure called "Average Length"

Avg Length = 
var a = average('T 1'[Column1])
var y = INT(a / 365)
var m = int(divide(Mod(a, 365),30))
var d = int(mod(mod(a, 365), 30))
return format(y, "####") & " Years " & format(m, "####") & " months " & format(d, "####") & " days"

[AliceInChains · Accepted solution]

Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers

[AliceInChains · Accepted solution]

Based on:

Formulas:

TodaysDate      = FORMAT(TODAY(),"mm/dd/yyyy")

Years              = FLOOR(YEARFRAC(Source[StartDate],[TodaysDate]),1)

Months           = FLOOR(MOD(YEARFRAC(Source[StartDate],[TodaysDate]),1) * 12.0,1)

Days              = SWITCH(DAY(Source[StartDate]) > DAY([TodaysDate]),

TRUE(), (DAY(EOMONTH([TodaysDate],-1)) - DAY(Source[StartDate])) + (DAY([TodaysDate])),

FALSE(), (DAY([TodaysDate])-DAY(Source[StartDate])))

Avg Length =

var a = average('T 1'[Column1])

var y = INT(a / 365)

var m = int(divide(Mod(a, 365),30))

var d = int(mod(mod(a, 365), 30))

return format(y, "####") & " Years " & format(m, "####") & " months " & format(d, "####") & " days"

The code below takes the Start Date and compares with Todays date and the difference is returned via the final field ctYMD in the format “nnn Years nn Month nn Days” I did this so when sorting “all is good” … I also embellished it to consider singular/plural for Year(s), Month(s) Day(s)…

The Fields in Red & Blue can be substituted for your own…

**********************************************************************************************

ctYears =

IF(AND(([Tenancy Start Date]) <> BLANK(),

        FLOOR(YEARFRAC([Tenancy Start Date],

                       TODAY()),1)=1),

        Concatenate (Format((FLOOR(YEARFRAC

                    ([Tenancy Start Date],

                     TODAY()),1)),"000")," Year "),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          FLOOR(YEARFRAC([Tenancy Start Date],

                         TODAY()),1)>1),

          Concatenate (Format((FLOOR(YEARFRAC(

                      [Tenancy Start Date],

                      TODAY()),1)),"000")," Years "),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          FLOOR(YEARFRAC([Tenancy Start Date],

                         TODAY()),1)=0),

          Concatenate (Format((FLOOR(YEARFRAC(

                      [Tenancy Start Date],

                      TODAY()),1)),"000")," Years "))))

/* Nested if statements that first check that Tenancy Start Date is not Blank then calculates the number of years and applies the appropriate Singular/Plural of Year(s) to the text returned */

***********************************************************************************************

ctMonths =

IF(AND(([Tenancy Start Date]) <> BLANK(),

        (FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                            TODAY()),1)*12,1))=1),

        Concatenate (Format((FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                                                TODAY()),1)*12,1)),"00")," Month "),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          (FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                              TODAY()),1)*12,1))>1),

          Concatenate (Format((FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                                                TODAY()),1)*12,1)),"00")," Months "),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          (FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                              TODAY()),1)*12,1))=0),

          Concatenate (Format((FLOOR(MOD(YEARFRAC([Tenancy Start Date],

                                                TODAY()),1)*12,1)),"00")," Months "))))

/* Nested if statements that first check that Tenancy Start Date is not Blank then calculates the number of months and applies the appropriate Singular/Plural of Year(s) to the text returned */

ctDays =

IF(AND(([Tenancy Start Date]) <> BLANK(),         (SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

TRUE(), (DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date]))))=1),

        (Concatenate (Format((SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

      TRUE(),(DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date])))),"00")," Day")),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          (SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

TRUE(), (DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date]))))>1),

          (Concatenate (Format((SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

      TRUE(),(DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date])))),"00")," Days")),

  IF(AND(([Tenancy Start Date]) <> BLANK(),

          (SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

TRUE(), (DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date]))))

=0),

          (Concatenate (Format((SWITCH(DAY([Tenancy Start Date]) > DAY(TODAY()),

      TRUE(),(DAY(EOMONTH('Property and Admin Unit'[TodaysDate],-1)) - DAY([Tenancy Start Date])) + (DAY('Property and Admin Unit'[TodaysDate])),FALSE(), (DAY('Property and Admin Unit'[TodaysDate])-DAY([Tenancy Start Date])))),"00")," Days")))))

/* Nested if statements that first check that Tenancy Start Date is not Blank then calculates the number of days and applies the appropriate Singular/Plural of Year(s) to the text returned */

ctYMD =

CONCATENATE( [ctYears],

CONCATENATE( [ctMonths], [ctDays]))

/* Concatenate ctYears, ctMonths and ctDays to arrive at the format nnn Years nn Months nn Days */

To calculate Summary Data use the following when aggregating…

nDays = if( [Tenancy Start Date]<>Blank(), (DATEDIFF( [Tenancy Start Date], TODAY(),DAY)),Blank())

/* Used to calculate the days difference between two dates … this is required for aggregation summaries */

MeanDays =

format(INT((average('Property and Admin Unit'[nDays])) / 365), "000") & " Years " &

format(INT(divide(Mod((average('Property and Admin Unit'[nDays])), 365),30)), "00") & " Months " &

format(INT(mod(mod((average('Property and Admin Unit'[nDays])), 365), 30)), "00") & " Days"

/* Converts average nDays into XXX Years XX Months XX Days */

MedianDays =

format(INT((Median('Property and Admin Unit'[nDays])) / 365), "000") & " Years " &

format(INT(divide(Mod((Median('Property and Admin Unit'[nDays])), 365),30)), "00") & " Months " &

format(INT(mod(mod((Median('Property and Admin Unit'[nDays])), 365), 30)), "00") & " Days"

/* Converts Medain nDays into XXX Years XX Months XX Days */

Output: