March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 ?
Solved! Go to 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.
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 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
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.
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 |
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 |
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
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
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
I put "title" instead of "Id" to present more meaningful data as follows,
Thanks @v-alq-msft for your continoues help !
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
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.
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.
@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
Check if this can help.
Also, check the file attached where I sum up age. Instead of age you try have hours
Appreciate your Kudos.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |