Why is index not being used? Can I replicate plan?
This was totally a workaround, but it worked for me.  I had 4 servers running the same database with different data sets.  Three of them would chose an optimal plan, but the fourth would never choose that plan, and was consistently taking significantly longer.  In my case I could see the good plans seeking one of the tables for a handful of rows out of around 100,000 first, then doing the other joins.  The bad plan would do that seek last and would seek that table 500,000 plus times.  I also tried the query hints and re-writing the query in different ways with no luck.
So my work around was to manually query that table for those handful of rows first into a temp table, then join to that instead of the full table.
So in your case could you run something like this first into a temp table and then join against it instead of the whole DWH.dbo.tbl_dim_date table:
select  d.dim_date_id 
   ,d.date
   ,d.flag_future_day 
from    DWH.dbo.tbl_dim_date d 
where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'  
          or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
   and d.flag_future_day = 'N'
   and d.date &gt;= '14/07/2020'

Not sure in your case if the above query would be selective enough to be really helpful and I realize that it is a workaround. But sometimes workarounds work!
Another thought, are you able to modify that NCI you included? It might help if you added the date column to the indexed columns instead of the INCLUDE columns. There are a lot of filters done in that query based on that date column.