topic Count days of a contract in a period in DAX Commands and Tips

Count days of a contract in a period

Margreet — Tue, 15 Jul 2025 17:37:58 GMT

Hello,

Hope someone knows the answer... My formula to conunt the numer of days in a year a contract is active don't work. I use the following formule:

_TotalDays of a contract in period =

VAR StartPeriode = MIN(Datumtabel[Date])

VAR EindPeriode = MAX(Datumtabel[Date])

RETURN

SUMX(

FILTER(

F_Contracten,

-- Alleen toewijzingen die overlappen met de periode

NOT(ISBLANK(related(D_contracten[Ingangsdatum]))) &&

NOT(ISBLANK(related(D_contracten[einddatum]))) &&

related(D_contracten[Ingangsdatum]) <= EindPeriode &&

RELATED(D_contracten[einddatum]) >= StartPeriode

DATEDIFF(

MAX(RELATED(D_contracten[Ingangsdatum]), StartPeriode),

MIN(related(D_contracten[einddatum]), EindPeriode),

DAY

) + 1

with this as result:

This is not what i want to see i want in each year the contract is active see the the days belong to dat year:

for example: key1:
in 2022: 1-8-2022 - 31-12-2022: 153 days
in 2023: 1-1-2023 - 31-12-2023: 365 days
in 2024: 1-1-2024 - 1-10-2024: 274 days

Thanks a lot for reaction!

Re: Count days of a contract in a period

FBergamaschi — Tue, 15 Jul 2025 18:08:10 GMT

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

Re: Count days of a contract in a period

Margreet — Tue, 15 Jul 2025 18:25:11 GMT

Thank!

Key	Productkey	Ingangsdatum	Einddatum
1	10	1-8-2022	1-10-2024
2	11	1-12-2022	31-12-2022
3	8	1-12-2022	1-6-2023
4	10	1-3-2023	1-8-2023
5	11	1-3-2023	1-3-2024
6	22	1-3-2023	1-3-2025

Re: Count days of a contract in a period

techies — Tue, 15 Jul 2025 18:43:11 GMT

Hi Margreet is this the year-wise breakdown you are looking for?

Re: Count days of a contract in a period

Margreet — Tue, 15 Jul 2025 18:56:18 GMT

hi techies , Yeah i am looking for the formula for that outcome... How did you do that?

Re: Count days of a contract in a period

techies — Tue, 15 Jul 2025 19:23:44 GMT

Hi Margreet as per the table that you have shared, create a disconnected year table like this

YearTable = DISTINCT(SELECTCOLUMNS(Datumtabel, "Year", YEAR([Date])))

and then create the total active days measure like this

Total Days =

VAR SelectedYear = SELECTEDVALUE(YearTable[Year])

RETURN

IF (

NOT ISBLANK(SelectedYear),

VAR YearStart = DATE(SelectedYear, 1, 1)

VAR YearEnd = DATE(SelectedYear, 12, 31)

RETURN

SUMX (

D_contracten,

VAR ContractStart = D_contracten[Ingangsdatum]

VAR ContractEnd = D_contracten[einddatum]

VAR OverlapStart = MAX ( ContractStart, YearStart )

VAR OverlapEnd = MIN ( ContractEnd, YearEnd )

RETURN

IF (

OverlapStart <= OverlapEnd,

DATEDIFF ( OverlapStart, OverlapEnd, DAY ) + 1,

)

SUMX (

D_contracten,

VAR ContractStart = D_contracten[Ingangsdatum]

VAR ContractEnd = D_contracten[einddatum]

RETURN

IF (

NOT ISBLANK(ContractStart) && NOT ISBLANK(ContractEnd),

DATEDIFF(ContractStart, ContractEnd, DAY) + 1,

)

Re: Count days of a contract in a period

Margreet — Tue, 15 Jul 2025 19:43:23 GMT

hi techies super! Thanks a lot, it worked in my pbix!