Duration calculation within a month

[Frelon]

Dear all,

I'm struggling with a DAX measure which would allow me to measure the duration of unavailability of an item within a given month selected in a filter.
I have crawled several topics raised on "duration calculation" but I sadely could not find any clues to solve my issue. I'm quite new to DAX too and I honestly don't have any idea what would be a correct start for such an issue.

As an example, I have a simple "REPAIR" table which contains some items and their period of unavailability:

ID	From	To	Duration
Item1	15/01/2021	20/01/2021	6
Item1	28/01/2021	03/02/2021	7
Item2	06/01/2021	08/01/2021	3
Item3	25/12/2020	02/01/2021	9
Item4	05/02/2021	10/02/2021	6

The duration column is a calculated column added on M side simply by substracting "From" and "To" columns.

The thing is I would like to use a month filter allowing me to know the duration of unavailibility of each piece during the selected month only.

For example when January is selected, I would know that:
- Item1 has been unavailable 10 days (6 days from 15th to 20th + 4 days from the 28th to the 31st of Jan.)
- Item 2, 3 days
- Item3, 2 days only (from the 1st of January till the 2nd of January) and not 9 days which is the total period of unavailability
- Item4 has not been unavailable (no entry on January)



I have created 2 calendar tables. The first one is linked to the "From" column of the REPAIR table. The second calendar is not linked to anything yet, I use it for my Month filter.

Thank you very much in advance for any help you may provide!

KR
Frelon

[Jihwan_Kim · Accepted solution]

Hi, @Frelon 

Please check the below picture and the sample pbix file's link down below.

I suggesting having a data model like below.

I added one more column in a fact table (Primary key column) in order to identify the same-items-different-period in the same month, if there are any.

Unavailable days count =

SUMX (

CROSSJOIN (

VALUES ( Dates[Month & Year] ),

VALUES ( Items[Item] ),

VALUES ( Repair[ID] )

),

CALCULATE (

CALCULATE (

COUNTROWS ( Dates ),

FILTER (

Dates,

Dates[Dates] >= SELECTEDVALUE ( Repair[From] )

&& Dates[Dates] <= SELECTEDVALUE ( Repair[To] )

)

)

)

)

https://www.dropbox.com/s/u828pgvt9funa0c/frelon.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Frelon]

Thank you so much @Jihwan_Kim! I had a look at your file, it works exactly as expected. I'm going to adapt to my report.
Thanks once again!