Data modeling with date, time & time period dim tables

[rjose]

Hi,

I want to create a Power BI model with date (calendar), time (hours) & Timeperiod (YTD, MTD etc.). Below is the current structure, calendar & time table are connected to fact tables. I'm not able to make the relationship b/w Time & Timeperiod bidirectional. This is messing up the last year calculations using dateadd. What would be the best way to model this data?

[bolfri]

Based on what you said:

---

@rjose wrote:

I want to create a Power BI model with date (calendar), time (hours) & Timeperiod (YTD, MTD etc.). 

I thought the Timeperiod is a table that agreggates dates to dynamic calculations.

Without sample data (even few rows and expected results showed in excel/paint) it's hard to say what model would be best for your scenario. Can you provide this pbix file with those table and few rows from fact table?

[rjose]

[bolfri]

Hi, can you share excel file with sample data like you've shown it on screenshot? 🙂

[rjose]

I'm not able to attach the file to this thread 😞 It says .xlsx is not supported

[bolfri]

You can use https://wetransfer.com/ and give a link here.

[rjose]

I have the sample file in my work laptop and my organization doesn't allow use of external file sharing services

[bolfri]

Why Time table is connected to Timesperiod? YTD i MTD it's bassicly a period names that should be connected to Calendar only. What am I missing here?

[rjose]

I need time period to start at particular hours for events. For example, event A is from Jan 10 6 PM to Jan 15 11:59 PM. Wasn't able to capture this without having time in 'Timeperiod' table