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:DOBTodayDatedif 14/12/19618/12/201720448 15/12/19618/12/201720447 16/12/19718/12/201716794 17/12/19808/12/201713505 Average 17798.548 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 VersionMeanDays =Concatenate (DATEDIFF(0,(AVERAGE([nDays])),YEAR) & " Years ", CONCATENATE(DATEDIFF(0,(AVERAGE([nDays])),MONTH) & " Months ", DATEDIFF(0,(AVERAGE([nDays])),DAY) & " Days ")) Can anyone help?

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

Forum Discussion

AliceInChains

Frequent Visitor

8 years ago

Solved

DateIF Help

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?

Need Help

AliceInChains
8 years ago
Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers
AliceInChains
8 years ago
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:

3 Replies

parry2k
Super User
8 years ago
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
  Frequent Visitor
  8 years ago
  Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers
  - AliceInChains
    Frequent Visitor
    8 years ago
    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:

Forum Discussion

DateIF Help

3 Replies

Related content

cambiar el número entero reportado en minutos a la duración en minutos