Datediff of several periods that fall within a range

[NassBhorr]

Hi!

I've been trying to get the total of days from different periods that fall within a range. I have a table with PERHH_PERSO (key for worker), with several PERHH_CONTRATO (number of contract), each with a PERHH_FEC_INI (start date) and PERHH_FEC_FIN (end date). 
For each contract, I establish a range from the Start_date to +364 days, in a calculated column (Day_end_period).

I also have a calculated column for the Datediff of the actual contract (Actual_ctr_days)
What I need is to count all days from all contracts of that same person (PERHH_PERSO) that fall within that range (from PERHH_FEC_INI to Day_end_period)

Here is an extract of the table, with just one person selected:

I'm not getting the logic of the calculation here... have tried several approaches but none is working.
Help will be much appreciatted.

Thanks!

[VillyMBI]

Hi @NassBhorr 

Are you looking for DAX for calculated column?
Then below DAX may be helpful to you

Calculate Days =

VAR _startDate = [PERHH_FEC_INI]

VAR _endDate = [Day_end_period]

VAR _person = [PERHH_PERSO]

RETURN CALCULATE(SUM([Actual_ctr_days]),
FILTER('Per Contract',  
[PERHH_PERSO] = _person &&
[PERHH_FEC_FIN] >= _startDate &&
[PERHH_FEC_FIN] <= _endDate &&
NOT(ISBLANK([PERHH_FEC_FIN])
)

)

I hope this will help you.

[NassBhorr]

Hi, 

Thanks for the response :). I don't want to sum the "Actual_ctr_days", I want to count all days that fall within the range PERHH_FEC_INI + 364. If I sum the datediff of all periods that end between the start and end of the range I might be adding days from a period that begins before the range. 

Also, that DAX contains circular dependencies.

Thanks!

[v-yiruan-msft]

Hi @NassBhorr ,

What's your expected result? Could you please provide some examples to explain your requirement base on your sample data? For example: person 102700, do you want to get the number of days between Day_end_period 08/12/2023 and 14/01/2025?

If yes, you can create a calculated column as below to get it:

Column = 
VAR _perso = 'Table'[PERHH_PERSO]
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Day_end_period] ),
        FILTER ( 'Table', 'Table'[PERHH_PERSO] = _perso )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Day_end_period] ),
        FILTER ( 'Table', 'Table'[PERHH_PERSO] = _perso )
    )
RETURN
    DATEDIFF ( _mindate, _maxdate, DAY )

Best Regards

[NassBhorr · Accepted solution]

Hi @v-yiruan-msft !

Thanks for the answer. What I'm trying to achieve is the count of days of all the periods (contracts, from PERHH_FEC_INI to PERHH_FEC_FIN) overlapping with the main range (PERHH_FEC_INI to Day_end_period). 
I just got a working formula with this logic, like this:

Total_Overlap_Days =

SUMX(

    FILTER(

        ALL(DataTable),

        DataTable[PERHH_PERSO] = EARLIER(DataTable[PERHH_PERSO])

    ),

    VAR CurrentStart = EARLIER(DataTable[PERHH_FEC_INI])

    VAR CurrentEnd = EARLIER(DataTable[Day_end_period])

    VAR ContractStart = DataTable[PERHH_FEC_INI]

    VAR ContractEnd = IF(DataTable[PERHH_FEC_FIN] = BLANK() || DataTable[PERHH_FEC_FIN] > TODAY(), TODAY(), DataTable[PERHH_FEC_FIN])

    VAR OverlapStart = MAX(CurrentStart, ContractStart)

    VAR OverlapEnd = MIN(CurrentEnd, ContractEnd)

    VAR OverlapDays = IF(OverlapEnd >= OverlapStart, DATEDIFF(OverlapStart, OverlapEnd, DAY) + 1, 0)

    RETURN

        IF(OverlapStart <= CurrentEnd, OverlapDays, 0)

Seems to work fine!
Thanks