Filtering within a date range

[Anonymous]

 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.

[v-juanli-msft · Accepted solution]

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]

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]

@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]

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]

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]

@AlB,

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

Best Regards,

Ajay

[AlB]

@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.

[AlB]

@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.