Show Tasks in specific month using date slicer

[Haya]

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	Sara	12/1/2019	2/1/2020	60
Task 4	Haya	2/2/2020	2/5/2020	4

I want to show all tasks in specific month, so I used Slicer for Start Date to show all tasks in specific month.

However, I found that this is logically wrong. Because if I put the date range for March it will not show me Task 1. Even that employee is working on Task 1 in March but because its start date is on February. 

What would be the best solution ?

[Anonymous]

Hi @Haya 

try this

Create calender table and don't connect it with any other table.

ADD date column in between slicer.

Create measure,

Measure=Sumx(table,if(Table[End date])<=MAx(date[Date]) && Table[End date])>=Min(date[Date]),1,0)

Add this measure to visual level filter and set it to "is not 0".

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

---

@Anonymous wrote:

Hi @Haya 

try this

Create calender table and don't connect it with any other table.

ADD date column in between slicer.

Create measure,

Measure=Sumx(table,if(Table[End date])<=MAx(date[Date]) && Table[End date])>=Min(date[Date]),1,0)

Add this measure to visual level filter and set it to "is not 0".

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

---

Have you tried this solution?

Thanks,

Pravin

[v-alq-msft · Accepted solution]

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]

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]

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]

Thanks @v-alq-msft 

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

for example:

Name	# Tasks
Haya	2
Sara	2
Ali	1

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
Haya	2

where it should be 

Name	#Tasks
Haya	1

[v-alq-msft]

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]

Would you please try to select March 2019

@v-alq-msft 

Does it show task 1 and task 4?

[v-alq-msft]

Hi, @Haya 

Here is the result. Only task 1 appears.

Best Regards

Allan

[Haya]

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

[v-alq-msft]

Hi, @Haya 

There is a many-to-many relationship betwwen 'id' and 'Assigned to'. So for 'Employee 24' it exsits both situations when the conditions are met and  situations when the conditions are not met. The result will be 2.

You may try to add 'Id' to 'Rows' in 'Fields' ribbon. Here is the result.

Best Regards

Allan

[Haya]

I put "title" instead of "Id" to present more meaningful data as follows,

Thanks @v-alq-msft  for  your continoues help !

[Mariusz]

Hi @Haya 

Please see the attached file with a solution + a video step by step.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Haya]

Thanks @Mariusz 

I followed your steps, however I couldn't make relation between Table and Table (Referenced). this error appears

Also I have some due dates that are empty so I couldn't appy ... operator

[Mariusz]

Hi @Haya 

You can add an index column to your "Table" table to make the records unique and later use Index columns to create relationship.

In reference to blanks due to null dates, you can replace them with today's date, for example, I've made the adjustment to the code in the attached file to accommodate null scenario.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Haya]

Thanks @Mariusz 

The problem of your approach that it duplicates the tasks when I extract the Date to new rows.

It affects the whole Data, so when I count for example number of tasks for each employee it returns incorrect data.

[amitchandak]

@Haya ,

I am not completely on the issue.  But these two file address a similar issue. Have a look

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

[amitchandak]

Check if this can help.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Also, check the file attached where I sum up age. Instead of age you try have hours

Appreciate your Kudos.