Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Activities between a date range

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    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.

     

  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous 

    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.

     

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Anonymous 

    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'

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        jdbuchanan71
        Super User
        6 years ago

        Hello Anonymous 

        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.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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