Number of days of a contract in certain year

[elmarhogenboom]

Hi everyone,

Same, same but different from this question.

Within a table, two columns are [Start of contract] and [End of contract], where [End of contract] can be empty for a current contract. A calculated column is added with the duration of the contract, with the following code. I use this for averaging the length of contracts, for instance.

Duration = 
IF(
'Contracts'[End of contract]>0;
[End of contract]-[Start of contract]+1;
TODAY()-'Contracts'[Start of contract])

A contract is related to a unit (apartment). When an apartment is vacant, there's also a contract, called a vacancy contract. 

Now, I'd like to calculate the number of days of vacancy in a given year for an apartment building. So for a vacancy contract starting Dec. 1st 2019, and still active today, the number of days in 2019 is 31 and in 2020 it'll be 225 and counting (today is Aug. 12th). I'd like to slice this into quarters and months as well. Then later, I want to calculate the percentage of vacancy (number of vacant days in a particular year / (total unit days in the  same year)).

Any help is greatly appreciated!

Cheers, Elmar

[elmarhogenboom · Accepted solution]

Hi @Ashish_Mathur ,

Thanks again for your reply.

Please find my Excel file here. 

Cheers, Elmar

[Ashish_Mathur]

Hi,

Share the link from where i can download your data in an MS Excel file.  If possible, please also show the expected result there.

[elmarhogenboom · Accepted solution]

Hi @Ashish_Mathur ,

Thanks again for your reply.

Please find my Excel file here. 

Cheers, Elmar

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[elmarhogenboom]

Thanks @Ashish_Mathur ,

That does help. I didn't figure you'd have to multiply the contractID table with all dates. Seems like a suboptimal solution from Power BI, not to be able to break down the duration between two days. Doesn't this explode the size of the file and speed of calculations? My contract table is only about 25 columns, with some 10.000 current and expired contracts.

Is it better to make a different table with only ContractID and date and link it to the Contract table, or to add the dates to the existing Contract table? 

And how to add a date column to an existing table, getting the result in your example? I only found this topic that describes the same question, but unfortunately it hasn't been solved.

Cheers, Elmar

[Ashish_Mathur]

You are welcome.  It will increase the file size and slow down calculations.  I don't think creating a seperate table will help.  In the Query Editor, i have used the M language to create one row for every date.  Hope this clarifies.

If my previous reply helped, please mark that as Answer.

[AllisonKennedy]

@elmarhogenboom
Do you have a DimDate table?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

A Date table will enable you to slice any of the measures you create by year, quarter, month, etc.

[elmarhogenboom]

Hi Allison,

Thanks for your reply. I have included a date table in my model.

Cheers, Elmar