Forum Discussion

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( [...

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:

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

Related content

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