There could be a simpler way to do this, but my (quick) solution is the following:
1) Don't use a calculated dimension. Define Month in the script.
2) Pad missing values (in the script) by using Peek():
RawData: Load * From Source;
CartesianProduct: Load distinct Month Resident RawData;
Join Load distinct Department Resident RawData;
Join (RawData) Load * Resident CartesianProduct;
Data: Load *,
If(IsNull(Headcount) and Department=Peek(Department),Peek(CorrectedHeadcount),
Headcount) as CorrectedHeadcount
Resident RawData Order By Department, Month;
Drop Table RawData, CartesianProduct;
3) Use the following calculation in the chart:
If( Count(distinct Month)=1,
If( Count(distinct Department)=1,
Thanks a lot for your solution that does provide the correct results.
I do have two comments:
1/ In such a case, I would love to be able to create the chart without having to change the data model.
2/ I'm affraid this formula might not work very well with big data volumes.
It would be great if we could have semi-additive functions built in. I know it does exist in SQL Server for example.
Do you think there would be any chance to have this in future versions?