Filter with the CALENDAR (range slicer) and with the options “last 7 days” and “last 60 days

[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!

[v-kongfanf-msft · Accepted solution]

Hi @game1 ,

You can try formula like below:

NumberPeopleLastDays_slicer =
VAR MaxSelectedDate =
    MAX ( 'Calendar'[Date] )
VAR MinSelectedDate =
    MaxSelectedDate
        - CALCULATE (
            MAX ( slicer[Value] ),
            FILTER ( slicer, slicer[last_] = SELECTEDVALUE ( slicer[last_] ) )
        )
RETURN
    CALCULATE (
        SUM ( Table1[numberPeople] ),
        FILTER (
            Table1,
            Table1[dateExam] >= MinSelectedDate
                && Table1[dateExam] <= MaxSelectedDate
        )
    )

create slicer table and calendar table:

Best Regards,
Adamk Kong

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

[Fowmy]

@game1 

To calculate for any time period based on the current date selection, follow the following pattern. you can chagne the Day to Month, Quarter ans so on,

Last 7 Days = CALCULATE( [Sales Amount] , DATESINPERIOD( 'Date'[Date] , MAX( 'Date'[Date] ) , -7 , DAY ) )

. In this example, the sales amount is calculated for the past 7 days from the current date selection. It takes the max of the current range.

[game1]

No! It is not working. I can select 2 options last 7 days or last 60 days and I need also the calendar (range slicer).

[Fowmy]

@game1 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[game1]