Filter a matrix on dates calculated in measures

[Anonymous]

Hello,

I have a date table D_DATES with the columns: DATE_ID, DATE and WEEK
The column WEEK contains the date of the beginning of the week (Monday) of the date 

I  use a slicer that is based on the column DATE

Based on this slicer I calculate following measures
The first calculates the date of the Monday of the week in which the selected minimum date of the slicer belongs

The second calculates the date of the Sunday of the week in which the selected maximum date of the slicer belongs

Week Begin Selected Minimum Date =

VAR _mindate_ =

       CALCULATE(

              MIN(D_DATES[DATE]);

              ALLSELECTED(D_DATES[DATE])

       )

VAR _weekday_ = WEEKDAY(_mindate_; 2)    -- 1 = Monday ... 7 = Sunday

RETURN

_mindate_ - _weekday_ + 1

Week End Selected Maximum Date =

VAR _maxdate_ =

CALCULATE(

MAX(D_DATES[DATE]);

ALLSELECTED(D_DATES[DATE])

)

VAR _weekday_ = WEEKDAY(_maxdate_; 2)

RETURN

_maxdate_ - _weekday_ + 7

e.g.
I choose in the date slicer the minimum date 18/3/2021, this date is part of the week that starts on Monday 15/3/2021
I choose in the date slicer the maximum date 23/8/2021, this date is part of the week that ends on Sunday 29/08/2021

If I show the values of these measures in cards:

I have an other table F_AMOUNTS with the columns: PK_AMOUNT, LEVEL, REGION, DATE and AMOUNT

Data model

The data from this table I show in a Matrix
Rows

       F_AMOUNTS[REGION]

       F_AMOUNTS[LEVEL]

Columns

       D_DATES[WEEK]

Values

       F_AMOUNTS[AMOUNT]

Is there a way to filter this Matrix on the two measures [Week Begin Selected Minimum Date] and [Week End Selected Maximum Date]?

For example by setting an other date slicer on these two values and use that slicer to filter the matrix. (just a suggestion if this is possible)

Anybody has an idea?

Thanks,

R.W.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can try to create a measure using the if statement and apply it to the visual level filter of this matrix.

The masure may like this:

measure = 
if(
    max(D_DATES[WEEK])>=[Week Begin Selected Minimum Date]&&
    max(D_DATES[WEEK])<=[Week End Selected Maximum Date],
1,
0 
)

Drag this measure into the visual level filter of the matrix and set the filter condition equal to 1

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.