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

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

2 Replies

  • 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!