Search code examples
sql-serverssasolap

SSAS fill missing gaps

lets say I have a fact table like this:

ID  Date      Amount
1   Jan2012    100
1   Feb2012    110
1   April2012  150

and a dimension dimDate that contains all months in 2012.

When browsing my cube I see exactly what's above, but I would like to see one row to all dates on the dimDate dimension but, whats more important, I would like to see the March2012 row with the value 110 (last available value)

Is it something that can be done on the cube? I know it can be done on the DW by inserting a row for april or maybe with a left join query but I want to know if it can be done on the cube

Solution

  • you can create a calculated measure. let's call it [CalcAmount] .the expression should be

    IIF(ISNULL([Measures].[Amount]),[Measures].[Amount].PREVMEMBER,[Measures].[Amount])

    hope that helps.