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.

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

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.