cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

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

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

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

3 ACCEPTED SOLUTIONS
Super User

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 =

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

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Super User

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

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Anonymous
Not applicable

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.

5 REPLIES 5
Anonymous
Not applicable

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.

Super User

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 =

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

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Anonymous
Not applicable

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?

Super User

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

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
Anonymous
Not applicable

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:

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