Birthday calculations


#1

Hi,
I have a list of customer details that includes their birthdays.
I need several things from this list.
The first is a list of customers that are having a birthday in the next month (not this month but the next one).
I get this by creating a virtual table

Birthdays in next month = 
FILTER(  'Customers',
    CONTAINS( ADDCOLUMNS(  CALENDAR( TODAY() + 1, TODAY() + 31),
            "Month", MONTH( [Date] ),
            "Day", DAY ( [Date] )  ),
                 [Month], MONTH( 'Customers'[Birth Date] ),
                 [Day], DAY( Customers'[Birth Date] )    )  )

This gives me the basic details of what I need for their birthday for next month.
I can replicate the same logic to give me the details for the current month but this seems a bit excessive. I was wondering if there was a better way to do both at once.

The second thing I need to do is identify the milestone birthdays (18, 21, 30, 40, 50, 60, 70, 80, 90, 100)

Finally, I want to create a visualisation where I show a summary of the number of birthdays per month.

Looking for ideas on how to achieve these goals

Thanks


#2

This honestly does seem a little complex.

What does your model look like? Any reason why you can’t just do this within measure? Why do you need to create a table like this?

If you have a date table you calculations should be relatively simple within measures?

You might have to potentially create a ‘customers’ like table, but place it as a fact table, and then have the dates as the lookup. Join the date column to the birth date column.

That would be the simpliest and easiest way to complete this in my view.


#3

Hi,
At the moment my model is just the customers table.
I thought about adding in the date lookup table but the potential issue there is the number of entries in the date table. It would have to cover 150 years just to be safe. That also seems excessive.
It may be more efficient to do it this way though.
I have been experimenting and totally open to options at this point. Nothing set in stone.


#4

Why would you need a date table that long (150 years)?

Seems unnecessary to me.

You mentioned you just want to find out how has a birthday next month? So why would you require 150 years in a table?