Not Trading on a Public Holiday causes blank Customer Count

[anthony_b]

Hi all,  I've exausted my standard 'google skills' and am seeking advice from the Big Dogs, specifically those well versed in Time Intelligence and how they operate.

The Background:

• With each year that passes 16-17, 17-18 we offset the Day of Week by 1 day. 26/01/2017 a Thursday becomes a Friday on 26/01/2018
• My goal is to display a table as shown below with the number of unique sales that store had to assist with their rostering efforts. Where if we were closed that  day and had '0' sales, to look back an additional week and display that value.

The Problem: 

If there were no sales due to us being closed that day appears blank, see the above yellow highlighted cell.

My Unsuccessful Solutions:

• Attempt 1: Generates table as shown above. Missing information from 26/01/18 due to no sales on 26/01/17

Visit Count - LY - Match Day = CALCULATE(SalesDetails[VisitCount]+IF(Salesdetails[VisitCount]=BLANK(),BLANK(),[- Guest Addon Per Day]),DATEADD('Calendar'[Date Time], -364, DAY))

• Attempt 2: Setting a condition where if the [VisitCount] is Blank() then search for a different interval.  
  Issues.  With a weekly column filter it finds the following week BUT alters all subsequent weeks values.

Visit Count - LY - Match Day w. offset = CALCULATE(SalesDetails[VisitCount]+[- Guest Addon Per Day],DATEADD('Calendar'[Date Time], IF(SalesDetails[VisitCount]>=23,-364,-357), DAY))

Has anyone else experienced a similar limitation? Or know of a solution or different approach to try?  Any insights would be greatly appreciated.

Thanks in advance.

[v-jianhe-msft]

Hi,

I am not quite clear about the SalesDetails[VisitCount] or [- Guest Addon Per Day]) you use.

So how is your dataset like? Could you share your pbix report? And basically, your requirement is to make the highlighted cell to show the which value?

BR,

Henry

[anthony_b]

Hi Henry,

Firslty, thanks for replying.  To clarify the additional measures in refererence:

SalesDetails[VisitCount] is the total number of sales from our detailed sales table. 

[- Guest Addon Per Day] is to assist me with a fixed value to simply inflate or deflate the sales (we can disregard it for my above inquiry)

The goal would be to make the highlighted cell show "60" as seen by the matched Thursday from the previous week.

Not sure if I am able to share our data as I don't yet have approval but I hope from the information above there is more to help theorise a solution.