Calculating Same Day Last Week only works partially in Power BI

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

 

[Matt Allington]

You should never write time intelligence functions over a transactional table. Create a calendar table, join it in the model, then use that, not the date column in your data table. There are many reasons why, but in this case, your IN statement is applying some filter to the data table. This can negatively impact the time intelligence calc. Having a calendar table abstracts the concept of contiguous days from other filters.

Here’s an article I wrote on the topic

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

 

[muhammadahmad21]

You should never write time intelligence functions over a transactional table. Create a calendar table, join it in the model, then use that, not the date column in your data table. There are many reasons why, but in this case, your IN statement is applying some filter to the data table. This can negatively impact the time intelligence calc. Having a calendar table abstract the concept of contiguous days from other filters.

Here’s an article I wrote on the topic

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

Thanks man, this worked! This was such a rookie mistake lol I even made a calendar table but I wasn't using it thinking I don't need it. Turns out I did. Thanks again!