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

 

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.

 

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 ?

 

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.

18 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

      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's avatar
        Mariusz
        Icon for Community Champion rankCommunity 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

         

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    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 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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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