Set Limit of Future Appointment within 1 Month

[Niiru1]

I have a measure that I need to grab all available doctors appointments for the next 1 month, 3 months, 6 months respcectively.

My issue is I cant get my measure to limit to appointments captured within 1 month.

When I run this measure:

Upcoming Appointments - 1 month = 
CALCULATE(
 AVERAGE('Table'[Days From Booking to Appointment]),
 DATESBETWEEN(
 'Appointment Times with date'[Date], 
 TODAY(), 
 MONTH(TODAY())+1 
 )
)

However this only grabs every appointment date from today onwards, is there a way to limit this particular measure to only grab dates from TODAY( ) & <= TODAY( ) +1 MONTH?

[v-yingjl · Accepted solution]

Hi @Niiru1 ,

You can use DATESINPERIOD() function to calculate period value like this:

Upcoming Appointments -1 month = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), 1, MONTH )
)

Upcoming Appointments -3 month = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), 3, MONTH )
)

Upcoming Appointments -6 month = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    DATESINPERIOD ( 'Calendar'[Date], TODAY (), 6, MONTH )
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Niiru1 , Try measure like

Upcoming Appointments - 1 month =
var _1 = today()
var _2 = date(year(_1) , Month(_1)+1, day(_1))
return
CALCULATE(
AVERAGE('Table'[Days From Booking to Appointment]),
DATESBETWEEN(
'Appointment Times with date'[Date],
_1,
_2
)
)