3 Replies Latest reply on Dec 6, 2018 11:39 AM by Alexander Mou

# Count days even if your data is missing days for the dimension

In the example below how would I created a count of business days to be used across all dimension even if the dimension doesn't have a record for the day.  It would also need to be flexible to work based on the date range of the view.

I can get dimension 12345 to return the correct number of days by summing INT({Fixed [Row_Date]:MIN([Is Business Day])}).  However for dimension 67890 it returns 16 because there are missing days.  I have no way of building the input to include the missing days for 67890.

 Dimension Date Business Day 12345 67890 1-Jul 0 0 0 2-Jul 1 1 3-Jul 1 1 1 4-Jul 0 0 0 5-Jul 1 1 1 6-Jul 1 1 1 7-Jul 0 0 0 8-Jul 0 0 0 9-Jul 1 1 10-Jul 1 1 1 11-Jul 1 1 1 12-Jul 1 1 1 13-Jul 1 1 1 14-Jul 0 0 0 15-Jul 0 0 0 16-Jul 1 1 17-Jul 1 1 1 18-Jul 1 1 1 19-Jul 1 1 1 20-Jul 1 1 1 21-Jul 0 0 0 22-Jul 0 0 0 23-Jul 1 1 24-Jul 1 1 1 25-Jul 1 1 1 26-Jul 1 1 1 27-Jul 1 1 1 28-Jul 0 0 0 29-Jul 0 0 0 30-Jul 1 1 31-Jul 1 1 1 Total 21 21 16
• ###### 1. Re: Count days even if your data is missing days for the dimension

How about create a new column [67890+] like

if 67890=Null then [Business Day] else [67890] end

then counting the days using this new column.

• ###### 2. Re: Count days even if your data is missing days for the dimension

Thanks for the suggestion but that wouldn't work in this case.  My illustration was miss leading.

The data coming in looks like the below.  For part 67890 there are no records for some of the business days yet I still need to include those days in calculations that use count of business days.  So regardless of how many records come in for each part the calculation needs to use the same number of business days across all parts.

 Part Date Business Day 12345 1-Jul 0 12345 2-Jul 1 12345 3-Jul 1 12345 4-Jul 0 12345 5-Jul 1 12345 6-Jul 1 12345 7-Jul 0 12345 8-Jul 0 12345 9-Jul 1 12345 10-Jul 1 12345 11-Jul 1 12345 12-Jul 1 12345 13-Jul 1 12345 14-Jul 0 12345 15-Jul 0 12345 16-Jul 1 12345 17-Jul 1 12345 18-Jul 1 12345 19-Jul 1 12345 20-Jul 1 12345 21-Jul 0 12345 22-Jul 0 12345 23-Jul 1 12345 24-Jul 1 12345 25-Jul 1 12345 26-Jul 1 12345 27-Jul 1 12345 28-Jul 0 12345 29-Jul 0 12345 30-Jul 1 12345 31-Jul 1 67890 3-Jul 1 67890 5-Jul 1 67890 6-Jul 1 67890 10-Jul 1 67890 11-Jul 1 67890 12-Jul 1 67890 13-Jul 1 67890 17-Jul 1 67890 18-Jul 1 67890 19-Jul 1 67890 20-Jul 1 67890 24-Jul 1 67890 25-Jul 1 67890 26-Jul 1 67890 27-Jul 1 67890 31-Jul 1
• ###### 3. Re: Count days even if your data is missing days for the dimension

Seems you already know

1) the start day and the end day of your period of interest

2) the business days in between.

There is no need for records. With zero records, we can still figure out the number of business days.

Right?