Forum Discussion
Add date on my fact table
Simple question, my current Fact table needs a date column, I have already two columns that represents YEAR and MONTH both are text, I need to create a new column "Date" based on both columns, to connect to my new date table.
Tks All
Hi fbittencourt,
Use Below Function in Power Query to create date column
#date( Number.FromText([Year]), Number.FromText([Month]), 1 )
or DAX
Date =
DATE ( VALUE ( FactTable[Year] ), VALUE ( FactTable[Month] ), 1 )🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi fbittencourt, Hope you are doing well!
You can achieve this by using a calculated column with DAX:
Use below DAX to create the calculated column:
Date =
DATE (
VALUE ( 'Fact'[Year] ),
VALUE ( 'Fact'[Month] ),
1
)
If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.
Proud to be a Microsoft Fabric community super user
Let's Connect on LinkedIn
Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.Hi fbittencourt ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank anmolmalviya05 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
Best Regards,
Community Support Team
4 Replies
Hi fbittencourt ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank anmolmalviya05 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
Best Regards,
Community Support TeamHi fbittencourt,
Use Below Function in Power Query to create date column
#date( Number.FromText([Year]), Number.FromText([Month]), 1 )
or DAX
Date =
DATE ( VALUE ( FactTable[Year] ), VALUE ( FactTable[Month] ), 1 )🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!your fact table has YEAR (ANNÉE) and MONTH (N_MOIS or MOIS) stored as text. To create a proper Date column in Power BI (so it can relate to your Date table), you can build a calculated column.
Here’s how:
Option 1 – Using DATE() function
If you have:
ANNÉE = "2023"
N_MOIS = "01"
You can create:
Date = DATE(
VALUE(FactTable[ANNÉE]),
VALUE(FactTable[N_MOIS]),
1
)
👉 This will give you the first day of the month (e.g., 2023-01-01).Option 2 – If you already have a combined column like MOIS = 202301
Use:
Date = DATE(
INT(FactTable[MOIS] / 100),
MOD(FactTable[MOIS], 100),
1
)
👉 For 202301, this results in 2023-01-01.Hi fbittencourt, Hope you are doing well!
You can achieve this by using a calculated column with DAX:
Use below DAX to create the calculated column:
Date =
DATE (
VALUE ( 'Fact'[Year] ),
VALUE ( 'Fact'[Month] ),
1
)
If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.
Proud to be a Microsoft Fabric community super user
Let's Connect on LinkedIn
Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.
