Modelling Date and Times , with Durations and exceeded targets etc

[Anonymous]

Hi all,

I have  requirement to handle lots of dates and times.

So an initial fact table will include such as

Recorded Date (and time)

Target Date (and time)

Completed Date (and time)

Original Target Date    (and time)

Appointment Date (and time)

all with realtionships to date table. Thats fine.            

A requirement will be durations - so durations in days , or hours or mins bewteen dates.

ie

Recorded Date to Completed Date

Target Date to Completed Date

Etc

Would you put all these time columns in the fact table. And even precalculate these durations in a fact table. Or use measures sumx and related to work out durations , date targets met or exceeded.

How would I store say the recorded date/time , so a self service person could easily incude that in their reports.

Any pointers or articles on this would be gratefully received. Thanks

[amitchandak · Accepted solution]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can try like

new column = datediff([Recorded Date],[Completed Date],Day)

new meausre= Averagex(Table,datediff([Recorded Date],[Completed Date],Day))

[Anonymous]

Thanks - after reading some sqlbi where they have stored the duration in the fact table. And some Kimball where they have stored times in teh fact table for now I will go with that method. Reducing my columns to only what strictly needed. Thanks