Connecting DATE table (Month/Year, WEEKNUM, etc.) to my other tables w/ disparate dates, efficiently
I'm fairly new to Power BI so this may be an easy one. I've spent a few days on the forum trying to find a clear answer to no avail.
I have a generic DATE table that I created (found online):
I also have about a dozen other tables with financial information, each with a variety of dates (Billing StartDate, EndDate, FundedThruDate, WeedEnded, etc.). For example, one of those tables (BI-BillingRuleUpdate):
I would like to find the most efficient way (I assume using a Measure) to populate these Financial tables (like BI-BillingRuleUpdate) with some of the columns from my DATE table, so that for StartDate (above), columns are created with Month of Year, WEEKNUM, etc. for that StartDate. E.g. for the first row (6/25/2018) I get columns that show StartDateMonth (June), StartDateWeekOfYear (26), etc. Then potentialy do the same thing for EndDate, FundedThruDate, etc., with the end goal of having my rows (of client billing amounts) grouped by weeks and months in my final chart/visual.
I've just been creating new columns in each table I want to do that in, but I'm not great with DAX and this way seems messy/inefficient to do for my dozens of tables. E.g.,
Month = MONTH('BI-BillingRuleUpdate'[EndDate])
Week of Year = WEEKNUM('BI-BillingRuleUpdate'[StartDate])
As you can see, I'm not "calling" my DATE table that I created here. I'm just using the MONTH and WEEKNUM expressions. I want to make use of my DATE table, efficiently!
I'm hoping that someone can help me write a template or find an easier method with Filters or doing something with my active/inactive relationships (maybe?) so that all of these disparate dates found in my Financials tables are automatically connected to my DATE table, so that each table can be populated and organized by Month of Year, Week Num, etc.
Side note: A colleague found something online to write a DAX measure that seems to aggregate the dates (in her case, for Employee Retention) and used it as her Axis/Filter for her chart. Should I be using VAR selectedDate and SUMX type expressions as well? It looks like this:
Anyway, there are probably many methods for populating my Financial tables' dates with Month of Year, WeekNum, etc. by connecting it to my DATE table. As I'm new to Power BI, I just don't know if the way I'm going about it is the most efficient, or if there's a simpler method so I can apply it to the dozens of tables I have with date values, but without the Month, Week breakdown.
There is really no need for all that complexity. If you have every date in your date table, and that table is connected by date or integer to every other table, then you can just use the week/month/quarter etc. right from the date table.
I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
"and that table is connected by date or integer to every other table..."
Could you clarify this?
Is this referring to Managing Relationship? Dragging my "Date" (in my case, a list of continuous dates from Jan 1 2000 to Jan 1 2025) to all of my other tables with a Date element in it (even if those dates aren't continuous)? If a table has more than what date column (e.g., StartDate, EndDate, WeekEnded, etc.) can I still apply to more than one (i.e. create more than one relationship)? And then would I just apply my Date table as a filter in my visuals without having to write a Measure?
Sorry, you might just be walking me through BI 101...I'm just at a mental block here...