Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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

I would like the following output:

IDActivityPlanned DateThe  "Planned Date" is within a loan period
1Activity1             2023-02-28              TRUE
2Activity32024-04-12FALSE
2Activity42023-10-27TRUE
2Activity12023-02-28TRUE
3Activity32022-07-14TRUE
3Activity42023-07-14TRUE
4Activity42024-06-14TRUE
4Activity32023-06-13TRUE
5Activity42024-04-26FALSE
6Activity32023-11-14TRUE
6Activity42024-11-11TRUE
7Activity32024-03-15TRUE
7Activity42024-09-20FALSE
7Activity12023-09-29FALSE
8Activity42024-10-27FALSE
8Activity22027-12-31FALSE

 

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 PlanningActivity Planning


Here is a link to download the Power BI Report:

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

3 ACCEPTED SOLUTIONS
SamInogic
Super User
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 =

        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.

SamInogic_0-1681895345945.png

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.

SamInogic_1-1681895364919.png

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

View solution in original post

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.

SamInogic_0-1681900439891.png


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/



Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

View solution in original post

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.

View solution in original post

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.

SamInogic
Super User
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 =

        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.

SamInogic_0-1681895345945.png

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.

SamInogic_1-1681895364919.png

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/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
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:
Activity planning2.PNG
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?

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.

SamInogic_0-1681900439891.png


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/



Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
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:

Activity planning3.PNG
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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors