Determine if a date falls within one of many date intervals in another table

[Anonymous]

I would like the following output:

ID	Activity	Planned Date	The  "Planned Date" is within a loan period
1	Activity1	2023-02-28	TRUE
2	Activity3	2024-04-12	FALSE
2	Activity4	2023-10-27	TRUE
2	Activity1	2023-02-28	TRUE
3	Activity3	2022-07-14	TRUE
3	Activity4	2023-07-14	TRUE
4	Activity4	2024-06-14	TRUE
4	Activity3	2023-06-13	TRUE
5	Activity4	2024-04-26	FALSE
6	Activity3	2023-11-14	TRUE
6	Activity4	2024-11-11	TRUE
7	Activity3	2024-03-15	TRUE
7	Activity4	2024-09-20	FALSE
7	Activity1	2023-09-29	FALSE
8	Activity4	2024-10-27	FALSE
8	Activity2	2027-12-31	FALSE

But I can't seem to be able to work it out.

The problem is that there are multiple Planned Dates per ID in the Activity TAble, and Multiple Loan Intervals in the Loan Table:

These are my fields/atbles and the link to the Power BI report is below:

Activity Planning

Here is a link to download the Power BI Report:

https://www.swisstransfer.com/d/e2c352c5-6edf-4012-8037-149997ea8f88

[SamInogic · Accepted solution]

Hi @Anonymous ,

You can try to create a Measure in your Activity table with below expression:

Activity in Intervals =

    VAR _SelectedStart = SELECTEDVALUE(Activity[Planned Date])

    VAR _EarlyStart = MIN(Interval[Loan Start])

    VAR _EarlyFinish = MAX(Interval[Loan End])

    VAR _Table =

        ADDCOLUMNS(

            Activity,

            "InSelection",

                SWITCH(

                    TRUE(),

                   ISBLANK(_EarlyStart) || ISBLANK(_EarlyFinish), FALSE(),

                   _EarlyStart >= _SelectedStart || _EarlyFinish >= _SelectedStart, TRUE(),

                     FALSE()

                )

        )

RETURN        

        SELECTCOLUMNS(

            _Table,

            "Selected", [InSelection]

            )

Please refer to the below screenshot for the same.

Now add this measure in the “Activity Table (multiple activities/planned dates per ID)” table and it will show True if it falls within Interval Date Start to Interval End range otherwise shows False.

Please refer to the below screenshot for the same.

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[SamInogic · Accepted solution]

Hi @Anonymous ,

I have used your attached report and added the newly created Measure in first table you have in your report. 
Also, as per your latest screenshot it seems that you have added a seperate table with columns as "ID", "Activity", "Planned Date", "Activity in Intervals" and I noticed that if we select ID from Activity table it shows the data as you given the screenshot and if we change it with ID from Transaction table it shows proper True false value.

Can you also verify if your table have ID from Transaction table where you are verifying the true/false interval fall measure?

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous · Accepted solution]

You are amazing. Quite correct, when I use the ID from the Transactions table it is correct. Thank YOU SO MUCH. This really has helped me a lot.

[SamInogic · Accepted solution]

Hi @Anonymous ,

You can try to create a Measure in your Activity table with below expression:

Activity in Intervals =

    VAR _SelectedStart = SELECTEDVALUE(Activity[Planned Date])

    VAR _EarlyStart = MIN(Interval[Loan Start])

    VAR _EarlyFinish = MAX(Interval[Loan End])

    VAR _Table =

        ADDCOLUMNS(

            Activity,

            "InSelection",

                SWITCH(

                    TRUE(),

                   ISBLANK(_EarlyStart) || ISBLANK(_EarlyFinish), FALSE(),

                   _EarlyStart >= _SelectedStart || _EarlyFinish >= _SelectedStart, TRUE(),

                     FALSE()

                )

        )

RETURN        

        SELECTCOLUMNS(

            _Table,

            "Selected", [InSelection]

            )

Please refer to the below screenshot for the same.

Now add this measure in the “Activity Table (multiple activities/planned dates per ID)” table and it will show True if it falls within Interval Date Start to Interval End range otherwise shows False.

Please refer to the below screenshot for the same.

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

Hmmm, when I apply the suggested measure (THANK YOU so much :-)) I don't get the same results as in you r screenshot. Your screenshot is correct but I am getting the following (wrong) results in for example ID 2 Activity 3:

I tried harmonising the date format using the FORMAT function but this didn't help either.

Any suggestions as to why we are getting different results?

[SamInogic · Accepted solution]

Hi @Anonymous ,

I have used your attached report and added the newly created Measure in first table you have in your report. 
Also, as per your latest screenshot it seems that you have added a seperate table with columns as "ID", "Activity", "Planned Date", "Activity in Intervals" and I noticed that if we select ID from Activity table it shows the data as you given the screenshot and if we change it with ID from Transaction table it shows proper True false value.

Can you also verify if your table have ID from Transaction table where you are verifying the true/false interval fall measure?

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

Hi again. I am sorry that I am getting the wrong values when a planned date is inbetween activities.

I have added a new ID "9" with one value before minimum date and one value inbetween activities. Then I am getting the wrong answer:


Here is the link to the updated file:
https://www.swisstransfer.com/d/1aa2dcad-1654-4ec2-9f70-f3512509d6a6 

Do you have an idea as to how to correct this?