Filter Activities between a date range

[jnnilesh29]

Hi Everyone, 

I have a table with the list of activities with start date and finish date.

Please refer the table below:

I'm trying to create a lookahead schedule. For example, I want to list down the activities that fall under the date range of June 01, 2020 to June 30, 2020. Then again create the list of activities that fall under the date range of June 01, 2020 to July 31, 2020 and like wise for 5 months from June 01, 2020.

I did a lot of search and came up with the following code but it is not working . I may be completely wrong as I'm new with Power BI.

One Month Lookahead = 
VAR startdate = DATE(2020,06,01)
VAR finishdate = DATE(2020,06,30)
Return
SELECTEDVALUE('Activity Status'[Activity Name],FILTER('Activity Status','Activity Status'[Start]>=startdate &&'Activity Status'[Finish]<=finishdate))

Can somebody help me with this?

Appreciate your help in advance.

Reagrds,

Nilesh Jain

[v-alq-msft · Accepted solution]

Hi, @jnnilesh29 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create a measure as below.

Visual Control = 
var _min = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _max = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Activity],
    'Table'[Start],
    'Table'[Finish],
    "flag",
    IF(
        NOT(
            OR(
                [Start]>_max,
                [Finish]<_min
            )
        ),
        1,0
    )
)
return
SUMX(
    tab,
    [flag]
)

Then you need to put the measure in the visual level filter and use the 'Date' column from 'Calendar' table to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @jnnilesh29 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create a measure as below.

Visual Control = 
var _min = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _max = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Activity],
    'Table'[Start],
    'Table'[Finish],
    "flag",
    IF(
        NOT(
            OR(
                [Start]>_max,
                [Finish]<_min
            )
        ),
        1,0
    )
)
return
SUMX(
    tab,
    [flag]
)

Then you need to put the measure in the visual level filter and use the 'Date' column from 'Calendar' table to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jnnilesh29]

Hi Allan, 

Just to let you know, your solution also worked.

Thanks for your all efforts.

I appreciate your help.

Thanks and Regards,

Nilesh Jain

[jnnilesh29]

This is great. But I already have a calendar table connected to my activity table. 

Also, It would be greatly helpful if I can get the help to filter the activities that are either starting in June 2020 or finishing in June 2020. Using the date slicer for June 01, 2020 to June 30, 2020 does not capture the activties that are started before June 2020 or will be finishing after June 2020.

If needed, I can share my file with you and that way it would be easy for you to figure out.

Thanks and Regards,

Nilesh Jain

[jdbuchanan71 · Accepted solution]

Hello @jnnilesh29 

We can modify the measure a bit to turn off the relationship between the Dates table and the 'Activity Status' table just when doing the [Active Count] using CROSSFILTER:

Active Count No Join = 
CALCULATE(
    VAR _Start = FIRSTDATE ( DATES[Date] )
    VAR _End = LASTDATE ( DATES[Date] )
    RETURN
    CALCULATE(
        COUNTROWS('Activity Status'),
        'Activity Status'[Start] <= _End,
        'Activity Status'[End] >= _Start
    )
    ,CROSSFILTER('Activity Status'[Start],DATES[Date],None)
)

In my sample file the date table is linked and the normal count on catches the joined month, like you are seeing.  The updated meassure ignores the link when calculating so it returns on the months from start to finish. 

In the screenshot below the date slicer is filtering only the table below it and you can see that my date range is set to Feb 2019 and the table is showing all projects where the active period crosses Feb-2019.

I have attached my sample file for you to look at.

[jnnilesh29]

Genius. 

I really appreciate your efforts to put together the sample sheet. 

Thanks again!

Regads,

Nilesh Jain

[jdbuchanan71]

@jnnilesh29 

Can you share that sample in a table rather than a picture so we can copy and paste it? 

It will be something along these lines.

You will need a disconnected date table and that is what you will use to select your date range.

Then a measure like this to use as a filter.

Active Count:=
VAR MinDate = FIRSTDATE ( DATES[Date] ) 
VAR MaxDate = LASTDATE ( DATES[Date] ) 
RETURN
    CALCULATE (
        COUNTROWS(YourTable),
            YourTable[Finish] >= MinDate,
            YourTable[Start] <= MaxDate
    )

Then you apply that measure as a filter on your visual and set it to 'is not blank'

[jnnilesh29]

Thanks for your reply. 

The Calendar table in my file is connected to various columns in the Activity Status column. Disabling them is creating issue with other visuals. 

It would be greatly helpful if I can get the help to filter the activities that are either starting in June 2020 or finishing in June 2020. Using the date slicer for June 2020 does not capture the activties that are started before June 2020. 

If needed, I can share my file with you and that way it would be easy for you to figure out.

Thanks and Regards,

Nilesh Jain