Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zoemostert
Helper I
Helper I

Show name of the upcoming birthday

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. 

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

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. Smiley Embarassed

 

Edit Edit: I made the same mistake twice!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

17 REPLIES 17
v-chuncz-msft
Community Support
Community Support

@zoemostert,

 

You may use RANKX Function to add a calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
KHorseman
Community Champion
Community Champion

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. Smiley Embarassed

 

Edit Edit: I made the same mistake twice!





Did I answer your question? Mark my post as a solution!

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")

 





Did I answer your question? Mark my post as a solution!

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")




Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




voorbeeld.png

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")




Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.