Forum Discussion

Haya's avatar
Haya
Helper II
6 years ago
Solved

Show Tasks in specific month using date slicer

I have the following table as an example   Task Assigned to Start Date End date Duration Task 1 Haya, Sara 2/15/2019 4/15/2019 60 Task 2 Ali 1/1/2020 5/1/2020 120 Task 3 Sa...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Haya 

     

    Based on your description, I created data to reproduce your scenario.

     

    You may create a Date table as follows.

     

    Calendar = CALENDARAUTO()

     

     

    Then you can create a measure as below.

     

    IsProcess = 
var _mindaterange = 
CALCULATE(
    MIN('Calendar'[DateRange]),
    FILTERS('Calendar'[DateRange])
)
var _maxdaterange = 
CALCULATE(
    MAX('Calendar'[DateRange]),
    FILTERS('Calendar'[DateRange])
)

return
IF(
    NOT OR(_maxdaterange<MAX('Table'[Start Date]),_mindaterange>MAX('Table'[End Date])),
    1,0
)

     

     

    Finally you need to put the measure to the visual level filter. Here are the results.

     

    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's avatar
v-alq-msft
Community Support
6 years ago

Hi, Haya 

 

Based on your description, I created data to reproduce your scenario.

 

You may create a Date table as follows.

 

Calendar = CALENDARAUTO()

 

 

Then you can create a measure as below.

 

IsProcess = 
var _mindaterange = 
CALCULATE(
    MIN('Calendar'[DateRange]),
    FILTERS('Calendar'[DateRange])
)
var _maxdaterange = 
CALCULATE(
    MAX('Calendar'[DateRange]),
    FILTERS('Calendar'[DateRange])
)

return
IF(
    NOT OR(_maxdaterange<MAX('Table'[Start Date]),_mindaterange>MAX('Table'[End Date])),
    1,0
)

 

 

Finally you need to put the measure to the visual level filter. Here are the results.

 

Best Regards

Allan

 

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

Haya's avatar
Haya
Helper II
6 years ago

Thanks v-alq-msft 

I followed your step but the output is incorrect

would you please explain this line of the code:

 

return
IF(
    NOT OR(_maxdaterange<MAX('Table'[Start Date]),_mindaterange>MAX('Table'[End Date])),
    1,0
)
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Haya 

     

    The formula excludes the wrong results, where the minimum value of date range is greater than the current 'End Date' or the maximum value of date range is less than the current 'Start Date'. Then the remaining cases satisfy the condition.

     

    Best Regards

    Allan

    • Haya's avatar
      Haya
      Helper II
      6 years ago

      Thanks v-alq-msft 

      I followed your approch and I used matrix to visual number of tasks for each employee

      for example:

      Name# Tasks
      Haya2
      Sara2
      Ali1

       

      Using the slicer, I want to show the tasks in specific month (ex. March), however, using your approach, if one of the tasks is on March, it will show me all number of tasks.

      Example: select March in date slicer, it will show me

      Name# Tasks
      Haya2

      where it should be 

      Name#Tasks
      Haya1
      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Haya 

         

        Here is the example you provided.

         

        Then I created the following measure to count tasks.

        Tasks = 
COUNTROWS(ALLSELECTED('Table'))

         

        Here is the result.

         

        There is only 'Ali' satisfy the condition which is between 3/1/2020 and 3/31/2020. So the result is 1. I wonder if there is any difference with the sample data.

         

        Best Regards

        Allan