Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Haya
Helper II
Helper II

Show Tasks in specific month using date slicer

I have the following table as an example

 

TaskAssigned toStart DateEnd dateDuration
Task 1Haya, Sara2/15/20194/15/201960
Task 2Ali1/1/20205/1/2020120
Task 3Sara12/1/20192/1/202060
Task 4Haya2/2/20202/5/20204

 

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

data renge.PNG

 

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 ?

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Haya 

 

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

b1.png

 

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.

b2.png

b3.png

 

Best Regards

Allan

 

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

View solution in original post

18 REPLIES 18
Anonymous
Not applicable

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
Not applicable


@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
Community Support
Community Support

Hi, @Haya 

 

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

b1.png

 

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.

b2.png

b3.png

 

Best Regards

Allan

 

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

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
)

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

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

Hi, @Haya 

 

Here is the example you provided.

d1.png

 

Then I created the following measure to count tasks.

Tasks = 
COUNTROWS(ALLSELECTED('Table'))

 

Here is the result.

d2.png

 

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

Would you please try to select March 2019

@v-alq-msft 

Does it show task 1 and task 4?

Hi, @Haya 

 

Here is the result. Only task 1 appears.

d4.png

 

Best Regards

Allan

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

 

june.PNG

 

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

 

see records.PNG

 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

 

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.

 

e1.png

e2.png

e3.png

e4.png

 

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

e5.png

 

Best Regards

Allan

 

 

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

demo3.PNG

 

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

Mariusz
Community Champion
Community Champion

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

 

Thanks @Mariusz 

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

Capture.PNG

 

 

 

 

 

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

Mariusz
Community Champion
Community Champion

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.

image.png

 

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

 

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
Super User
Super User

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.