Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have a table with names and birthdays. I want to show the name and the birthday of the person who's birthday is the nearest to todays date.
Solved! Go to Solution.
If there are multiple people with the same birthdate this will only return the name of the first person in your table. If you want a list of people when there is more than one, we'll have to do this a slightly different way.
Also this assumes you don't want to see anyone whose birthday is today. If you want to include today, there is a > in this formula that you should change to >=
Upcoming Birthday = VAR next = CALCULATE(
MIN(BirthdayTable[Birthday]),
FILTER(
BirthdayTable,
MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) &&
DAY(BirthdayTable[Birthday]) > DAY(TODAY())
)
)
RETURN CALCULATE(
FIRSTNONBLANK(BirthdayTable[Name], 1),
FILTER(
BirthdayTable,
MONTH(BirthdayTable[Birthday]) = MONTH(next) &&
DAY(BirthdayTable[Birthday]) = DAY(next)
)
)
Edit: I made a very silly mistake in my first version of this formula.
Edit Edit: I made the same mistake twice!
Proud to be a Super User!
You may use RANKX Function to add a calculated column.
If there are multiple people with the same birthdate this will only return the name of the first person in your table. If you want a list of people when there is more than one, we'll have to do this a slightly different way.
Also this assumes you don't want to see anyone whose birthday is today. If you want to include today, there is a > in this formula that you should change to >=
Upcoming Birthday = VAR next = CALCULATE(
MIN(BirthdayTable[Birthday]),
FILTER(
BirthdayTable,
MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) &&
DAY(BirthdayTable[Birthday]) > DAY(TODAY())
)
)
RETURN CALCULATE(
FIRSTNONBLANK(BirthdayTable[Name], 1),
FILTER(
BirthdayTable,
MONTH(BirthdayTable[Birthday]) = MONTH(next) &&
DAY(BirthdayTable[Birthday]) = DAY(next)
)
)
Edit: I made a very silly mistake in my first version of this formula.
Edit Edit: I made the same mistake twice!
Proud to be a Super User!
Thank u! it worked for the name, but the matching birthday is not showing up.
Oh I missed that part of your request. I'll assume you want to ignore the year and just say the date like "Sep 22" but you can change the formatting to whatever you want.
Upcoming Birthday = VAR next = CALCULATE( MIN(BirthdayTable[Birthday]), FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) && DAY(BirthdayTable[Birthday]) > DAY(TODAY()) ) ) RETURN CALCULATE( FIRSTNONBLANK(BirthdayTable[Name], 1), FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) = MONTH(next) && DAY(BirthdayTable[Birthday]) = DAY(next) ) ) & " - " & FORMAT(next, "MMM dd")
Now, let's imagine you want to get a list if there are multiple people with the same birthday. The formula above will just give the first person's name (I am not sure if it's the literal first entry in the table or the first alphabetically). But what if Bob and Steve both have the same birthday? You don't want Steve to feel left out. Steve will be sad.
Upcoming Birthdays = VAR next = CALCULATE( MIN(BirthdayTable[Birthday]), FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) && DAY(BirthdayTable[Birthday]) > DAY(TODAY()) ) ) RETURN CONCATENATEX( FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) = MONTH(next) && DAY(BirthdayTable[Birthday]) = DAY(next) ), FIRSTNONBLANK(BirthdayTable[Name], 1), ", " ) & " - " & FORMAT(next, "MMM dd")
Proud to be a Super User!
hmmm i didnt check if the outcome was correct. i checked and the result is false. it shows me 28 november, but there are birthdays on the 22nd and the 12th of November.
Is there anything that may be filtering your page or visual in the report? Any slicers or other visuals? Try this instead.
Upcoming Birthday = VAR next = CALCULATE( MIN(BirthdayTable[Birthday]), FILTER( ALL(BirthdayTable), MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) && DAY(BirthdayTable[Birthday]) > DAY(TODAY()) ) ) RETURN CALCULATE( FIRSTNONBLANK(BirthdayTable[Name], 1), FILTER( ALL(BirthdayTable), MONTH(BirthdayTable[Birthday]) = MONTH(next) && DAY(BirthdayTable[Birthday]) = DAY(next) ) ) & " - " & FORMAT(next, "MMM dd")
Proud to be a Super User!
No there is not. i don't understand why it's not working
It works fine in my tests. Can you provide a sample data set that produces incorrect results?
Proud to be a Super User!
this is the table i'm using. the dates are in dutch, but i think u can understand what it says?
Oh I see what I did wrong. I forgot to ignore the year when finding the earliest birthdate.
Upcoming Birthdays = VAR next = MINX( FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) >= MONTH(TODAY()) && DAY(BirthdayTable[Birthday]) > DAY(TODAY()) ), CALCULATE( DATE( YEAR(TODAY()), MONTH(MIN(BirthdayTable[Birthday])), DAY(MIN(BirthdayTable[Birthday])) ) ) ) RETURN CONCATENATEX( FILTER( BirthdayTable, MONTH(BirthdayTable[Birthday]) = MONTH(next) && DAY(BirthdayTable[Birthday]) = DAY(next) ), FIRSTNONBLANK(BirthdayTable[Name], 1), ", " ) & " - " & FORMAT(next, "MMM dd")
Proud to be a Super User!
i get this message;
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
😞
You must have missed something when you were translating my formula to your real table and column names. What is the exact formula you're using now?
Proud to be a Super User!
Hi,
I've been looking for a solution to this as well, and this is the closest topic I can find online. However, I can't get it to function. I have a sharepoint-list with columns for names, birthdates etc. When entering your last formula, I don't see how it's supposed to work?
I've entered it as a measure - then I apply it as a value in the normal Table-view in Power BI. I've also tried Matrix, but no luck.
Tableview shows all name-values listen in a continous string. When I drag the Name-column into the table as well - hoping for two columns (Name - Upcoming birthday) - two columns are showing only the names, side by side. At the bottom all names are listed as total.
I thought the formula for this would be much simpler? And also written as a column-formula?Something like this:
BirthdayCountdown = TODAY() - DATE(YEAR(TODAY());MONTH(BirthdayTable[Birthday].[Month]);DAY(BirthdayTable[Birthday].[Day]))
The formula I gave you is a measure. You've entered it as a column.
Proud to be a Super User!
Yes because nothing happend when i used it as a measure
Oh the last one did, but still showed me the wrong name
Did you display the measure on a card visual?
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |