Forum Discussion
Helper IIIFY Date Table & Multiple Date Columns in Fact Table
Hi, all.
I'm new to PBI and working through the Maven Analytics course while working on some data for my company at the same time. I have a fact table with multiple date/time columns that I need on a FY cadence for reporting. I looked up how to create a Date Table with a FY running from October to September, and I think it looks good. However, I don't know how to create a relationship between that table and my fact table with columns like "Assignment Date/Time," "Completion Date/Time," "Start Date/Time," for corporate trainings.
When I create a relationship between the tables, I have to pick one of the date columns. How do I do visuals based on FY dates for the columns I don't select? I know I'm missing something, including some pretty foundational knowledge, but haven't been able to find a resource to help me apply a FY date table/heirarchy to multiple date columns in one fact table. Help!
1 Reply
- some_bih
Community Champion
Hi Dave1mo1 your questions are good once.
Firstly, according to best practice it is the best to have single Calendar / Date table in Power BI.
If you want / need to use "second, third..." columns date type you need to use DAX function USERELATIONSHIPS.
For FY calendar details, I would suggest using external tool Bravo for Power BI as there is a lot time intelligence scenarios so you shuould find one/s you need.
It could be this is toooo much for you, but is worth reading and implementing
Links and articles relating to your scenario
https://www.daxpatterns.com/standard-time-related-calculations/ (search part fiscal and other (not everything is needed for you, currently))
USERELATIONSHIP - DAX Guide (second, third... date columns examples)
Bravo for Power BI by SQLBI (external tool)
Did I answer correctly? Kudos appreciate / accept solution.
