Forum Discussion

Frelon's avatar
Frelon
Frequent Visitor
5 years ago
Solved

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 rais...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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.

     

     

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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.

 

 

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

  • Frelon's avatar
    Frelon
    Frequent Visitor
    5 years ago

    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!