Rolling period with starting date issue

[Anonymous]

I have a list of dates in an appointments table. For all the dates in the appointments table, I want to measure how many appointments there are from x date in the appointments table in the coming 6 months.

Does anyone know how to do this? Currently, I have the following, however this is not a correct DAX formula as you cannot have Appointments[Date appointments] in that place. I don't know what the starting date should be as I want this calculation for every appointment date in the table.

Any help is appreciated!

Rolling appts =

CALCULATE(COUNT(Appointments[Date appointment]),
DATESINPERIOD(Calendar[Date], Appointments[Date appointment], 6, MONTH))

[lukiz84]

Hi,

try

VAR maxDate = MAX(Calendar[Date])

RETURN 

CALCULATE(
    COUNT(Appointments[Date appointment]),
    FILTER(
         ALL(Calendar),
         Calendar[Date] > maxDate && Calendar[Date] <= EDATE(maxDate, 6)
    )
)

BR

[MahyarTF · Accepted solution]

Hi, 

Use this measure :

CntOfApp = CALCULATE( count(Sheet39[name of appointer]),

                      'Date'[Date] > max('Date'[Date])

                      &&

                     'Date'[Date] <= EDATE(max('Date'[Date]), 6)

                    )

[Anonymous]

@lukiz84 Thank you for your reply! Unfortunately, this returns blank for me.

[lukiz84]

Hi,

even if you choose a date? When there's no date selected, then there are no appointments outside maxDate ... 

If you just want to - regardless of user selections - show the next 6 months, replace MAX(Calendar[Date]) with TODAY()

[Anonymous]

@lukiz84 This then shows the same value for all dates in the date table. I want it to show a different value for every date (provided that is correct). See image for reference.

[lukiz84]

yeah sorry,

just dont filter the date table via FILTER, add the Calculate params as @MahyarTF did, then it works.