Forum Discussion

Haya's avatar
Haya
Icon for Helper II rankHelper 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.

Haya's avatar
Haya
Icon for Helper II rankHelper 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
Icon for Community Support rankCommunity 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

  • Haya's avatar
    Haya
    Icon for Helper II rankHelper II
    6 years ago

    Would you please try to select March 2019

    v-alq-msft 

    Does it show task 1 and task 4?

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

      Hi, Haya 

       

      Here is the result. Only task 1 appears.

       

      Best Regards

      Allan

      • Haya's avatar
        Haya
        Icon for Helper II rankHelper II
        6 years ago

        Dear v-alq-msft 

        I'm counting the tasks in different way, based on its status. (may be this is why I'm getting wrong output)

        To clarify more, I upload a real sample data in this link: Sample 

        When I select June, this is the output

         

         

        When I right click on # task and click see records, here is the output

         

         The first date is start date and the second is end date.

        Task 84 is correct but Task 78 is not.

         

        Thank you for help