Calculation for number of days per month within different periods of time

[Anonymous]

Hi.

I have been trying without success to calculate the number of days within a given period of time that streches between different months. I need to display in a visual the number of days X month. 

The dataset I have is something like this:

As you can see, some periods begin in one month and end in the following.

The result I am looking for is as below, where the number of days used is placed in the respective month it belongs to:

Any help is appreciated! I am stuck with this issue for days...

Thanks!! 

[richbenmintz]

Hi @Anonymous,

As mentioned in the Video suggested, you can use a dsiconnected date table to generate the values you are looking for along with the following Measure

Days of Use = 
var _monthstart = MIN('Calendar'[Date])
var _monthend = MAX('Calendar'[Date])
return
SUMX('Table', 
    SWITCH(TRUE(),
        'Table'[Start Time] < _monthstart && 'Table'[End Time] < _monthstart  , BLANK(), // Blank for empty intersections
        'Table'[End Time] < _monthend && 'Table'[Start Time] >= _monthstart, DATEDIFF('Table'[Start Time], 'Table'[End Time], DAY), 
        'Table'[Start Time] >= _monthstart && 'Table'[End Time] >= _monthend && DATEDIFF('Table'[Start Time], _monthend, DAY) > 0, DATEDIFF('Table'[Start Time], _monthend, DAY),
        'Table'[Start Time] < _monthstart && 'Table'[End Time] <= _monthend, DATEDIFF(_monthstart, 'Table'[End Time], DAY)+1,
        BLANK())
)

Results in the following Matrices

I am also attaching a sample pbix with a simple calendar table and the measure.

I hope this helps.

[Syndicate_Admin]

good...
the truth is that the formula gives me results much closer to those of the video, but I have 2 drawbacks. when the stay for example is an annual stay, it adds the 365 days of the year in the subtotals but in the table or graphs it only shows me the month of beginning and closing.

and on the other hand randomly there are rows that do not count them

[Portrek · Accepted solution]

Hi lltat.

Look this video below, maybe helps you.

https://www.youtube.com/watch?v=R8tDKwimSsY

Best regards.

[Anonymous]

Thanks!! That was exactly what I was looking for! (★‿★)