title: MDX - Average over the whole time period, even when no data exists
body: <p>I have a fact table with 1 row for each day. Example:</p>
  
  <pre><code>ID  Date     Value
  1   20190101 10
  1   20190102 15
  2   20190101 31
  </code></pre>
  
  <p>If I take a simple Value average in SSAS cube I get:</p>
  
  <pre><code>ID Average &lt;Formula&gt;
  1  12.5    (10+15)/2
  2  15.5    31/2
  </code></pre>
  
  <p>As I understand, 15.5 is there because in total there are 2 days in the scope as only two days exist in the fact data when I select the whole month.</p>
  
  <p>However, I need to calculate a <strong>monthly</strong> average instead. It should check that there are 31 days in that month (based on Date dimension) and get this result:</p>
  
  <pre><code>ID Average &lt;Formula&gt;
  1  0.8     (10+15)/31
  2  1       31/31
  </code></pre>
  
  <p>So far I've tried to create some "fake rows" if my data, for example I've tried to create rows with Value = 0 for dates 20190103-20190131 for ID=1.</p>
  
  <p>This works, it forces the calculation for ID=1 to always take all days in the period, but it messes up my other calculations in the cube.</p>
  
  <p>Any other ways to force average calculation <strong>in SSAS multidimensional cube</strong> to always calculate for the entire month?</p>