Better way to count days withing a range and filter it by year

[fcol]

Let me try to exemplify what I need to do:

1. I have a calendar table;

2. I have a factories table with their start and end dates (when applicable):

Factory	Start End	End Date
Factory #1	10/jun/1995	05/oct/2020
Factory #2	13/nov/2000
Factory #3	05/may/2010	29/apr/2021

3. I need to create a measure that counts how many days the factory was functional when using the year filter from the calendar table.

I'm pretty sure it shouldn't be difficult but I'm having a hard time figuring it out.

[wdx223_Daniel · Accepted solution]

[Jihwan_Kim · Accepted solution]

Factory Functioning days count =

IF (

NOT ISBLANK ( SELECTEDVALUE ( Factories[End Date] ) ),

CALCULATE (

COUNTROWS ( Dates ),

FILTER (

Dates,

SELECTEDVALUE ( Factories[Start End] ) <= Dates[Date]

&& SELECTEDVALUE ( Factories[End Date] ) >= Dates[Date]

)

),

CALCULATE (

COUNTROWS ( Dates ),

FILTER (

Dates,

SELECTEDVALUE ( Factories[Start End] ) <= Dates[Date]

&& TODAY () >= Dates[Date]

)

)

)

https://www.dropbox.com/s/atkypekfzmmxpe9/fcol.pbix?dl=0 

[wdx223_Daniel · Accepted solution]