Forum Discussion
Post ProdigyDate table when year and week no columns exist in fact table
I have a fact table that contains the date, year and week number fields. The week number is specific for the company as it counts the holidays etc so cannot use the week number produced in power bi/power query. If I want to create a date dimension table then which of the two options are the best to go ahead with:
option 1. reference the fact table, keep only the columns date, year, week_no, then add a month and month name column, then remove duplicates.
option 2: create a date table say in dax, then add all the relevant columns year, month and month name, create a relationship with fact table date field. Then merge the week number on to the date table using the date columns on both tables.
Hi akhaliq7 ,
Your option 1 may not create a proper calendar table as you can't guarantee that your fact table contains contiguous dates, which is a specific requirement for a calendar table to work correctly.
You option 2 introduces merges and unneccesary complexity which will have to be replicated for each similar report.
To do this 'properly', I would suggest a third option:
Create a proper calendar table and add a [week_no] column to it using the logic that is appropriate for your business. Create this calendar in Dataflows so it is immediately available to any future report you build without having to recreate it each time.
Pete
1 Reply
- BA_Pete
Super User
Hi akhaliq7 ,
Your option 1 may not create a proper calendar table as you can't guarantee that your fact table contains contiguous dates, which is a specific requirement for a calendar table to work correctly.
You option 2 introduces merges and unneccesary complexity which will have to be replicated for each similar report.
To do this 'properly', I would suggest a third option:
Create a proper calendar table and add a [week_no] column to it using the logic that is appropriate for your business. Create this calendar in Dataflows so it is immediately available to any future report you build without having to recreate it each time.
Pete