Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
Seems to work fine!
Thanks
Hi @NassBhorr
Are you looking for DAX for calculated column?
Then below DAX may be helpful to you
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!
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
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:
Seems to work fine!
Thanks
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |