MDX - Average over the whole time period, even when no data exists

Question

I have a fact table with 1 row for each day. Example:

ID  Date     Value
1   20190101 10
1   20190102 15
2   20190101 31

If I take a simple Value average in SSAS cube I get:

ID Average <Formula>
1  12.5    (10+15)/2
2  15.5    31/2

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.

However, I need to calculate a monthly average instead. It should check that there are 31 days in that month (based on Date dimension) and get this result:

ID Average <Formula>
1  0.8     (10+15)/31
2  1       31/31

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.

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.

Any other ways to force average calculation in SSAS multidimensional cube to always calculate for the entire month?

Answer 1

If you want to do the calculation in the Cube, you can use the Descendants function on your Date dimension

For eg., the following gives the number of days in a month using the AdventureWorks sample

WITH MEMBER Measures.DayCount AS 
    Descendants
    ( 
        [Date].[Calendar].CurrentMember,   
        [Date].[Calendar].[Date],
        LEAVES  
    ).Count

SELECT [Measures].[DayCount] ON 0,
        [Date].[Calendar].[Month].ALLMEMBERS ON 1 
FROM [Adventure Works]

Answer 2

I would recommend:

select id, eomonth(date) as eom,
       sum(value) * 1.0 / day(eomonth(date)) as average
from t
group by id, eomonth(date);

EOMONTH() returns the last day of the month. You can extract the day to get the number of days in the month.

The * 1.0is because SQL Server does integer division. Your numbers look like integers, but if you are getting 15.5, then you actually have numerics or something other than an integer.