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 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?
Solved! Go to Solution.
Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers
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:
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"
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you I will give it a go ... is thsi writtin in DAX? If I sole I will post... Cheers
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:
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |