Search code examples
sqlssasmdx

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

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?

Solution

  • 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]

    enter image description here