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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    AlB,

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

     

    Best Regards,

    Ajay

    • AlB's avatar
      AlB
      Community Champion
      7 years ago

      Anonymous

      You can send it by private message here but well... if it's confidential I just would not share it with anyone

      Unless you can take off the confidential part completely and still be able to reproduce the issue at hand.