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.

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