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

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

Reply
Frelon
Frequent Visitor

Duration calculation within a month

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:

IDFromToDuration
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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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

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

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

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.

 

Picture1.png

 

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

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

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


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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.