Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fcol
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):

FactoryStart EndEnd Date
Factory #110/jun/199505/oct/2020
Factory #213/nov/2000 
Factory #305/may/201029/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
wdx223_Daniel
Super User
Super User

Jihwan_Kim
Super User
Super User

Picture1.png

 

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.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Picture1.png

 

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.


Go to My LinkedIn Page


wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1625013036164.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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