Filter date table based on start and end date from another table's selected rows

[Anonymous]

Hi everyone. I've spent the last couple of hours trying multiple arpproaches to this simple problem, but can't seem to work it out. I will highlight what it is I need and mention the things I've tried at the end

Here is the .pbix file and data to replicate my problem.

What I need:

In the following dashboard, I want the line plot on the right to ONLY show data for the date ranges selected in the table on the left. In other words, the [Date] column of the dji table should ONLY contain values between the [Start Service] and [End of Service] columns of the selected/highlighted rows of the table.

Figure 1

If multiple rows are selected with a non-continuous time period, I am okay with a solution that either:

1. Filters the data between MIN([Start Service]) and MAX([End of Service]) of all selected rows (Figure 2) OR
2. Filters data between each of those ranges in a non-continuous manner

What I've done so far (and failed):

• Attempted to create an intermediate date column (as follows) and establish a relationship with the DJI table

dates = ADDCOLUMNS(
    CALENDAR(MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]),MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
    "Year",YEAR([Date]),
    "Month",MONTH([Date]),
    "Day",DAY([Date]),
    "DayOfWeek",WEEKDAY([Date],2)
)

•  Created an Include measure in the DJI dataset

Include = 
    IF(
        (dji[Date] >= MINX(ALLSELECTED(pres_congress),pres_congress[Start Service]) && dji[Date] <= MAXX(ALLSELECTED(pres_congress),pres_congress[End of Service])),
        "Include",
        "Exclude"
    )

 In each of these cases, the resulting Table/Measure just did not update based on the selected rows.

[Alex_Sawdo]

There's a slightly easier way to this sort of thing. What you could do is write two seperate measures, BeginDate and EndDate. BeginDate will be:

BeginDate =

VAR V_Start =

CALCULATE(

    MIN([Start Service]

    )

)

VAR V_End =

CALCULATE(

    MAX(

        [End Service]

    )

)

RETURN

IF(

    V_Start <= V_End,

    V_Start,

    V_End

)

And end date would be the inverse of this. Then, in your actual calculation you can do this:

CALCULATE(

    [Your Thing To Calculate],

    FILTER(

        [Date Table],

        [Date Column] IN

        DATESBETWEEN(

            [Date Column],

            BeginDate,

            EndDate

        )

    )

)
You would most likely need to have an inactive connection with your date table for this to work, but it should be able to accomplish your goal. 

[CNENFRNL · Accepted solution]

So far, I'm able to project selected terms from pres_congres, whether contiguous or uncontiguous, to dji; but the performance of subsequent average calculation is terribly poor ... I'll leave it to you or others until I get some inspiration.

[Anonymous]

This is it sir. Performance on my computer is not an issue at all. I will probably find a way to optimize this in the future, but this is the solution I was looking for. Thank you so very much.

[amitchandak]

@Anonymous , These approaches can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[Anonymous]

Hi amitchandak. I went through your videos and I'm afraid I couldn't figure out how the resources will get me closer to what I want. Any chance you could use the PBIX and data files and share the solution?