Forum Discussion

sharmar4's avatar
sharmar4
Regular Visitor
7 years ago

Issue with Timeline Slicer

I am facing one issue in fliteration of my report. I am using timeline slicer so that i can filter my report yearly/qauterly/monthly.. 

I need to calculate the count of perosn who are active during the timeframe. For example :

Name StartDate EndDate
Rahul 01-Aug-19 25-Aug-19
Kamal 18-Jul-19 15-Oct-19
Naresh 25-Jun-19 10-Aug-19

 I have the above data set and i need to count the active person for month Aug. When i select the Aug month i am getting as count 1 wheras its should give me 3 as all the person are active in Aug month. 

Can someone please help me tyo achive that?

 

Need Help

1 Reply

  • Nishantjain's avatar
    Nishantjain
    Continued Contributor
    7 years ago

    sharmar4 

     

    I believe you are using the start date field in your slicer. If this is correct, you would appreciate that you only have 1 person with start date in August which is giving you the count as 1. 

     

    In order to get the right result, do the following

     

    1. Create a date table using

    Date table = CALENDARAUTO(12)
    2. Use the date from the date table in the slicer. Please do not have any relationship between the date table and your main table. 

     

    3. Create this measure to count the number of person

    Count People =
CALCULATE (
    COUNT ( Table2[Name StartDate EndDate.1] ),
    FILTER (
        Table2,
        Table2[Name StartDate EndDate.2] <= MAX ( 'Date table'[Date] )
            && Table2[Name StartDate EndDate.3] >= MIN ( 'Date table'[Date] )
    )
)