Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering within a date range

 I have a table of leaves with employee name along with start date and end date. i would like to make a chart to know how many are on leave on a particular date range.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    Create a calendar date table

    calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "day", DAY ( [Date] ),
    "week", WEEKNUM ( [Date], 2 )
)

    don't make relationship between these two tables

     

    in your data table (called "Sheet4" in my table)

    create columns:

    weeknum_start = WEEKNUM([from],2)

weeknum_end = WEEKNUM([to],2)

    create measures

    min = MIN('calendar'[Date])

max = MAX('calendar'[Date])

    add [week] column from table "calendar" in the slicer

    add [week] column from table "calendar" in the  table visual

     

    create measure 

    Measure 2 = CALCULATE(DISTINCTCOUNT(Sheet4[id]),FILTER(ALL(Sheet4),[from]<=[min]&&[to]>=[max]))

     

    Best Regards

    Maggie

     

     

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Create a calendar date table

    calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "day", DAY ( [Date] ),
    "week", WEEKNUM ( [Date], 2 )
)

    don't make relationship between these two tables

     

    in your data table (called "Sheet4" in my table)

    create columns:

    weeknum_start = WEEKNUM([from],2)

weeknum_end = WEEKNUM([to],2)

    create measures

    min = MIN('calendar'[Date])

max = MAX('calendar'[Date])

    add [week] column from table "calendar" in the slicer

    add [week] column from table "calendar" in the  table visual

     

    create measure 

    Measure 2 = CALCULATE(DISTINCTCOUNT(Sheet4[id]),FILTER(ALL(Sheet4),[from]<=[min]&&[to]>=[max]))

     

    Best Regards

    Maggie

     

     

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Maggie,

       Tried the way you have suggested. 

      i did get the count during the week. but of total employees

      I wanted to use few slicers to filter parameters. the measure is not picking up filters.

      Any suggestions

       

      Thanks And Regards,

      Ajay 

       

      is there a way i can share the pbix file out of the community ( confidential data)

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-juanli-msft

        Maggie,

         I could get the basics of what i want by appling filters at data level and generating multiple tables with teh same data with different filters.

        is there any way i can use filters/slicers and do the same in one data table

         

        Best Regards,

        Ajay

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    Try this:

     

    1. Create a Date table and leave it unrelated.

    2. Set the field of the 'Date' table you need on the x-axis of a chart, Month for instance

    3. Create this measure and place it in values of the chart:

     

    NumEmployeesOnLeave =
CALCULATE (
    DISTINCTCOUNT ( Table1[EmployeeName] ),
    FILTER (
        ALL ( Date ),
        Table1[LeaveEndDate] > MAX ( 'Date'[Date] )
            && Table1[LeaveStartDate] <= MAX ( 'Date'[Date] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks AlB..

      I could create a date table and tried the DAX you had suggested. 

      however when the measure is created and placed in value chart, the count is not filtered. it shows the total count of employees, than picking the date range. find below screen shots of the chart and the table. appreciate any help

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

        I don't see the pictures very well but you seem to be using a slicer and have weeks (from your data table?) on the rows of the matrix visual. I suggested to place a field of the Date table in the visual. If you want to do it like this you will need o create a relationship between your Date table and  your data table (table1). I think it would work with that. Otherwise, if you share the pbix (beware of confidential data) I will have a look.