Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.,
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.
Thank you for any guidance you can provide!
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.
--Nate
"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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |