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.

Mariusz's avatar
Mariusz
Community Champion
6 years ago

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's avatar
Haya
Helper II
6 years ago

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.