need help formule in powerBI

[Margreet]

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]))) &&

        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!!

[v-menakakota · Accepted solution]

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 

[v-menakakota]

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.

[v-menakakota]

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.

[v-menakakota]

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.

[Margreet]

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.

[HarishKM]

@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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Margreet]

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...