Calculating duration over time

[surena01]

Hello,

I'm having trouble with figuring out a formula calculating durations over time. 

In my fact table, I have my Records with Start Date and End Date. 

I have created a relationship between the Start and End Dates with my Calendar Table for Dates. 

The fact table has blank fields for both Start and End Dates. 

For calculating durations I would run into 3 scenarios:

1. Start Date is Blank: where Duration should result in Blank.

2. Start Date not Blank and End Date is Blank:  where the Duration should be based on the difference between Start Date and the Calendar Date greater than the Start Date until the End Date is not Blank. Once the End Date is not Blank the Duration should result in Blank on any date greater than the End Date. 

3. Start Date not Blank and End Date not Blank:  where the Duration is based on the difference between Start Date and Calendar Date until the End Date is not Blank. Any date greater that the End Date should result in Blank.

Maybe I'm overcomplicating the issue but the purpose of this calculation would be to Count the total records with Durations

above 14 days on any given day. 

Thank you in advance. 

[v-juanli-msft]

Hi @surena01

Actually, I don't understand the following statement.

"where the Duration should be based on the difference between Start Date and the Calendar Date greater than the Start Date until the End Date is not Blank. Once the End Date is not Blank the Duration should result in Blank on any date greater than the End Date"

Could you give me an example of what you want?

Best Regards

Maggie

[surena01]

Hi Maggie,

Visually in Excel I was trying to do something where for each date there would be a dynamic duration for every application.

[surena01]

Hello,

I'm having trouble with figuring out a formula calculating durations over time. 

In my fact table, I have my Records with Start Date and End Date. 

I have created a relationship between the Start and End Dates with my Calendar Table for Dates. 

The fact table has blank fields for both Start and End Dates. 

For calculating durations I would run into 3 scenarios:

1. Start Date is Blank: where Duration should result in Blank.

2. Start Date not Blank and End Date is Blank:  where the Duration should be based on the difference between Start Date and the Calendar Date greater than the Start Date until the End Date is not Blank. Once the End Date is not Blank the Duration should result in Blank on any date greater than the End Date. 

3. Start Date not Blank and End Date not Blank:  where the Duration is based on the difference between Start Date and Calendar Date until the End Date is not Blank. Any date greater that the End Date should result in Blank.

Maybe I'm overcomplicating the issue but the purpose of this calculation would be to Count the total records with Durations

above 14 days on any given day. 

Thank you in advance. 

[surena01]

This is the formula I have but it doesn't calculate dynamically.

DATEDIFF(
MAX(
MIN(fScheduleOverviewToday[Construction Actual Start Date]),
MIN(CalendarDate[Date])
),
MIN(
MAX(fScheduleOverviewToday[Construction Actual End Date]),
MAX(CalendarDate[Date])
),
DAY)