topic Better way to count days withing a range and filter it by year in DAX Commands and Tips

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

fcol — Tue, 29 Jun 2021 23:20:48 GMT

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.

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

wdx223_Daniel — Wed, 30 Jun 2021 00:30:50 GMT

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

Jihwan_Kim — Wed, 30 Jun 2021 03:10:30 GMT

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