Filter last 7 days and 60 days of selected range calendar

[game1]

Hello, 

I have Table1[dateExam] and Table1[numberPeople].

First, I need to use a CALENDAR (range slicer) to select a date (it must only consider the max date of range slicer selected). For example, I can select 10/01/2021 as my maximum date.

Second, I need to check one of the options: (in Segment). For example, I can check the option “last 7 days”.

Then, it would return me the numberPeople for the “last 7 days” of the selected date in the CALENDAR.(So the “last 7 days” is base on the CALENDAR (range slicer) I have selected.)

In other words, it would return me in a graphic, the numberPeople between 03/01/2021 and 10/01/2021.

For this example, in a graphic, I would have for each day between 03/01/2021 and 10/01/2021, the numberPeople that have done dateExam from Table1[dateExam].

Thanks!

[LufengYuan]

Hello 
You may try to use a field parameter as the segment slicer that consist of the measures like 'last7days','last10days'.
I created the following measure and seems works. Hope this helpful for you.

([Total People]  just a sum of Table1[numberPeople])

last 7 Days =

var _maxDate = MAX(Table1[dateExam])

var _result =

CALCULATE(

    [Total People],

    'Table1'[dateExam] > _maxDate - 7 &&

    'Table1'[dateExam] <= _maxDate

)

RETURN _result

[game1]

Hello, 

How did you apply the relation beetween the table Date(slicer) and Table1 ?

Thanks for your help! 

[game1]

Also, what kind of relation or cardinality did you apply to the table?

Can you post the screen shot or the file? 

Thanks!

[game1]

Hello,
Yes, I think this is close to what I want, but for your example, I see that the LAST 7 DAYS option, didn't really apply. This should be between 01/19/2020 to 01/25/2020 right (the column dateExam)?

Thanks!

[LufengYuan]

I think the last 7 days apply to the result as 126 = 15+16+...+21 . Here is the relationship, hope it is helpful for you. Sorry for the late reply as so busy on my job.

[game1]

Ok! But how do you do that the slicer 'Date' be functionning? 

Thanks!

[Anonymous]

HI @game1,

I'd like to suggest you take a look at the following blog about create segmentation table. You can use the segment field as source of slicer to filter your table records.

Static segmentation – DAX Patterns

Regards,

Xiaoxin Sheng

[game1]

Ok! But if I do CALENDAR as list check, like the option slicer? 

[Anonymous]

Hi @game1,

AFAIK, the default filter effects not able to achieve your requirement.

For this scenario, I'd like to suggest you create an unconnected table source of slicer. Then you can write a measure formula to receive the filter effect and use on visual to achieve filter effects.
Regards,

Xiaoxin Sheng

[game1]

Ok! But if I do CALENDAR as list check, like the option slicer?