Forum Discussion
AdministratorHours in Calendar
Hello everyone, good day
I have a fact table where there are date/time values, I made a calendar table and related them, but it doesn't work for me to filter, I guess it's because of the time, which is what I should do, how can I solve it, the time of the event matters
I remain attentive to your answers
Best regards
Hi Syndicate_Admin ,
I’d like to acknowledge the valuable input provided by lbendlin . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
You can create a new column in the fact table that extracts only the date portion from the date/time values.
You can do this in Power Query or using DAX.In Power Query, you can use the Date.From function:
DateOnly = Table.AddColumn(YourTable, “DateOnly”, each Date.From([DateTimeColumn]))In the Calculated column, you can use the DATE function:
DateOnly = DATE(YEAR([DateTimeColumn]), MONTH([DateTimeColumn]), DAY([DateTimeColumn]))Use this new date-only column to create a relationship with the calendar table.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- lbendlin
Super User
In your fact table create two additional columns, one for the date of the date/time value, and one for the time. Then join your calendar table to the date column, and your hour reference table to the time column.
Hi Syndicate_Admin ,
I’d like to acknowledge the valuable input provided by lbendlin . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
You can create a new column in the fact table that extracts only the date portion from the date/time values.
You can do this in Power Query or using DAX.In Power Query, you can use the Date.From function:
DateOnly = Table.AddColumn(YourTable, “DateOnly”, each Date.From([DateTimeColumn]))In the Calculated column, you can use the DATE function:
DateOnly = DATE(YEAR([DateTimeColumn]), MONTH([DateTimeColumn]), DAY([DateTimeColumn]))Use this new date-only column to create a relationship with the calendar table.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Syndicate_AdminHello
Thank you very much for your help, this worked out in a great way
Best regards
