topic Filter last 7 days and 60 days of selected range calendar in DAX Commands and Tips

Filter last 7 days and 60 days of selected range calendar

game1 — Sun, 14 Jan 2024 08:40:11 GMT

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!

Re: Filter last 7 days and 60 days of selected range calendar

Anonymous — Mon, 15 Jan 2024 09:03:01 GMT

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

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Mon, 15 Jan 2024 22:39:47 GMT

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

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Mon, 15 Jan 2024 22:50:36 GMT

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

Re: Filter last 7 days and 60 days of selected range calendar

LufengYuan — Tue, 16 Jan 2024 01:37:56 GMT

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

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Tue, 16 Jan 2024 04:48:43 GMT

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!

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Tue, 16 Jan 2024 04:47:47 GMT

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

Can you post the screen shot or the file?

Thanks!

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Thu, 18 Jan 2024 04:10:19 GMT

Hello,

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

Thanks for your help!

Re: Filter last 7 days and 60 days of selected range calendar

LufengYuan — Fri, 19 Jan 2024 23:34:26 GMT

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.

Re: Filter last 7 days and 60 days of selected range calendar

Anonymous — Mon, 22 Jan 2024 06:20:24 GMT

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

Re: Filter last 7 days and 60 days of selected range calendar

game1 — Mon, 22 Jan 2024 18:15:43 GMT

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

Thanks!