topic Re: Datediff of several periods that fall within a range in DAX Commands and Tips

Datediff of several periods that fall within a range

NassBhorr — Mon, 11 Mar 2024 11:39:24 GMT

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!

Re: Datediff of several periods that fall within a range

VillyMBI — Mon, 11 Mar 2024 13:33:35 GMT

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.

Re: Datediff of several periods that fall within a range

NassBhorr — Mon, 11 Mar 2024 14:00:23 GMT

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!

Re: Datediff of several periods that fall within a range

Anonymous — Tue, 12 Mar 2024 09:46:09 GMT

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

Re: Datediff of several periods that fall within a range

NassBhorr — Tue, 12 Mar 2024 10:00:08 GMT

Hi Anonymous !

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