Calculating Same Day Last Week only works partially

[muhammadahmad21]

I have this DAX that is supposed to return appointments for the same day last week by each brand for each date. It is working absolutely fine and giving me the desired results as I intended. The only issue is that for some brands, where there are 0 appointments for the current date, it shows 0 for the same day last week as well when there are actually appointments on the same day last week. The grand total number shows all appointments that matches with the same day last week but when I look at it by brand, it's missing some appointments.

Appointments SDLW =
VAR SameDayLastWeek = DATEADD('appointments_report'[START_DATE], -7, DAY)
VAR TotalAppointments = CALCULATE(COUNTROWS('appointments_report'),
'appointments_report'[APPOINTMENT_STATUS] IN {"Done", "Scheduled", "Working"},
SameDayLastWeek
)
RETURN
IF( ISBLANK(TotalAppointments), 0, TotalAppointments )

For more context, I have attached a screenshot of an example as well. See the highlighted 03/03/24, Sunday and 03/10/24, Sunday. The grand total number of Previous for 03/10/24, Sunday matches with the grand total number of Current for 03/03/24, Sunday but it only shows 8 for just 1 brand for the Previous 03/10/24, Sunday when clearly there are more in Current 03/03/24, Sunday for other brands. I noticed that whereever there are 0s in Current, it shows 0 as well in the Previous.

I hope I am able to clearly explain the issue. Could anyone please help me here as I have been scratching my head over this since last 3 hours.

[muhammadahmad21 · Accepted solution]

So turns out, I just needed a Calender table. I was performing the time intelligence function on the date column in the same table where I had the appoitments due to which I was facing this problem. Using a calendar table resolved my issue.

[lbendlin]

 where there are 0 appointments for the current date,

Or are there?  More like there are none, ie there is no data at all. 

To report on things that are not there you need to use disconnected tables and crossjoins.

[muhammadahmad21]

@lbendlin Yes there is no data in the current date but there is data in the previous date and I want to show number of appoitments for the previous date regarldless of if there is no data in the current date.

And I have a single table where all this data is coming from.

[lbendlin]

To report on things that are not there you need to use disconnected tables and crossjoins.

You need more than that single table.

[muhammadahmad21]

So if I have data in this row in the previous date and blank in current date, is that still a problem?

[lbendlin]

yes