Filtering within a date range

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.