edited*
I'm currently using Microsoft Power BI
Can someone provide the logic for "selecting all the birthdays that occured within the last 90 days relative to today's date"?
For example:
I have a table called "ClientTable" with the birthdays of 100 clients in an MM/DD/YYYY format. Today's date is 09/15/2022. I now want to list out all the of the clients that had a birthday within the last 90 days. So essentially, I am looking for a list of all the clients that had a birthday between 06/15/2022 and 09/15/2022.
This is how I attempted to solve it:
I first parsed out the Month and Day components of the BirthDate field into two separate columns. I then created a new table called "MasterDay" with all the dates in a single year and also parsed out the Month and Date components into separate columns:
MasterDate
| Date | Month | Day |
|---|---|---|
| DD/MM/YYYY | MM | DD |
| DD/MM/YYYY | MM | DD |
ClientTable
| BirthDate | Month | Day | ClientID |
|---|---|---|---|
| DD/MM/YYYY | MM | DD | 85 |
| DD/MM/YYYY | MM | DD | 7 |
I then created a relationship between the two tables "ClientTable" and "MasterDay" on their respective month and day columns. So that when placing the complete "Date" column from the MasterDay table into a slicer it would select all the corresponding rows from the "BirthDate" column in ClientDate table.
The issue I'm having is that when slicing for a range that begins in the middle of a particular month, say between 06/15/2022 and 09/15/2022, it picks up all of the birthdates in the months of June and September like 06/07/2022 or 09/23/2022 despite it not being included in the slicing range.
Thanks, G