cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS
Super User
Super User

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]
)
)
)

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

2 REPLIES 2
Super User

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]
)
)
)

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

Super User

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors