Measure for every date in date table

[Sohan]

I have a measure that counts the number of appointments in the coming 6 months for a given day:

OW = CALCULATE(COUNT(Appointments[Date appointment]),
DATESINPERIOD(Appointments[Date appointment], TODAY(), 6, MONTH))

For example: from February 9th until August 8th there are 1557 appointments. I have a seperate date table with all dates in it until 2025. For all dates in this date table, I want the above measure, however the Appointment[Date booking] may not be larger than the date in the date table.. Does anyone know how I can achieve this?

[Anonymous]

Hi  @Sohan ,

PBIX is not accessible, you can re-upload it.

I created some data:

Here are the steps you can follow：

1. Create measure.

OW =
CALCULATE(COUNT(Appointments[Date appointment]),FILTER(ALL(Appointments),'Appointments'[Date appointment]>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))&&'Appointments'[Date appointment]<=TODAY()))

2. Result:

Best Regards,

Liu Yang

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

[Sohan]

Hi @Anonymous thanks for your response! It unfortunately is not the solution I'm looking for. I have re-uploaded the file. I hope this clarifies things! PBIX file 

[amitchandak]

@Sohan , Create a date table with all the dates in Date appointment

Then create a measure for a future date
OW = CALCULATE(COUNT(Appointments[Date appointment]),
DATESINPERIOD(Date[Date], TODAY(), 6, MONTH))

past 6 month

OW = CALCULATE(COUNT(Appointments[Date appointment]),
DATESINPERIOD(Date[Date], TODAY(), -6, MONTH))

[Sohan]

Hi @amitchandak thanks for your reply! This is not exactly what I'm looking for. I have added a PBIX file with a sample dataset and an example of what I want. I hope this clarifies what I'm looking for.

Sample PBIX file