topic Re: need help formule in powerBI in DAX Commands and Tips

need help formule in powerBI

Margreet — Tue, 15 Jul 2025 16:18:53 GMT

Hello, hope someone kwow the right formula in DAX. I want to count the days in a contract is in a period. I use this 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]))) &&

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

)

the outcome left in the picture let see only the counts in the startperiode.
For key1: i want see in 2022 count days between 1-8-2022 to 31-12-2022 and in 2023: 365 en in 2024: count days 1-1-2024 to 1-10-2024

I hope someone knows this, thanks a lot!!

Re: need help formule in powerBI

lbendlin — Tue, 15 Jul 2025 23:25:12 GMT

The standard approach is to create two calendars (one for the contract duration and one for the filter context) and then use INTERSECT to see if they, well, intersect.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: need help formule in powerBI

HarishKM — Wed, 16 Jul 2025 06:09:04 GMT

Margreet Hey,
Use below measure

_TotalDays of a contract in period =

VAR StartPeriode = MIN(Datumtabel[Date]) VAR EindPeriode = MAX(Datumtabel[Date]) RETURN SUMX( FILTER( F_Contracten, 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 )

Ensure that your date table (Datumtabel) covers all necessary dates and that your F_Contracten table includes the relevant contract information.

This formula should help you accurately count the days for each contract that overlaps with the given period.

Thanks

Harish M

Please accepts this as a solution if it is solve your problem and give kudos as well

Re: need help formule in powerBI

Margreet — Wed, 16 Jul 2025 09:55:53 GMT

Hi HarishKM Thx voor your reaction, the outcome is not the expected. I see only the counts in the startperiode. This measure gives the same as my formule/question and is not good to use.

Re: need help formule in powerBI

Margreet — Wed, 16 Jul 2025 09:57:40 GMT

Hi lbendlin , thank for your reaction! I will try this. Or do you have a formula with Intersect? Thanks a lot.

Re: need help formule in powerBI

lbendlin — Wed, 16 Jul 2025 11:47:11 GMT

Re: need help formule in powerBI

v-menakakota — Thu, 17 Jul 2025 10:56:08 GMT

Hi Margreet ,
Thanks for reaching out to the Microsoft fabric community forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Community Support Team

Re: need help formule in powerBI

v-menakakota — Mon, 21 Jul 2025 05:04:34 GMT

Hi Margreet ,

We’re following up to check whether you were able to look into our earlier discussion. If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

Re: need help formule in powerBI

v-menakakota — Thu, 24 Jul 2025 06:01:53 GMT

Hi Margreet ,

If you're still facing challenges, sharing a sample dataset will enable us to provide a focused and effective solution.

We’re here to help if you have any additional questions.

Thank you.

Re: need help formule in powerBI

v-menakakota — Mon, 28 Jul 2025 05:03:12 GMT

Hi @Margreet ,

We’re here to help if you have any additional questions.

Thank you.