Calculating AVG with missing data in DM

[andigrill]

Hello,

how can I calculate the avg of a measure with missing data?

	Measure
11/01/2022	2
11/02/2022	4
11/03/2022
Sum	3

For 11/03/2022 there is no data, so the avg is calculated with 2 and 4, But I want the missing data used as 0 and the avg schould be 2.

I tried a measure as
    coalesce(total [Measure Column] /count (distinct [Date Column]),0)
but I got complete wrong results.

So I tried to calculate each part itself and create a calculated measure but somehow I get the wrong number of dates:

	Dim1.A				Dim1.B				Sum
	2022/11/01	2022/11/03	2022/11/05	Sum	2022/11/01	2022/11/03	2022/11/05	Sum	2022/11/01	2022/11/03	2022/11/05	Sum
AVGMeasure (Measue1/Number of Days)	1.813	1.870	1.878	1.854	456	450	452	453	1.135	1.160	1.165	1.153
Measure (Sum)	1.813	1.870	1.878	5.561	456	450	452	1.358	2.269	2.320	2.330	6.919
Number of Days	1	1	1	3	1	1	1	3	2	2	2	6

"Measure (Sum)" is the summarized Measure

"number of Days" is a calculated Measure 'count (distinct [Date])'
However, I didn't find a way for "Number of Days" to not summerize over other Dimensions (2 2 2 6  should be 1 1 1 3)!
When I tried default "number unique values" for [Date] I don't get a value for the Sum.

I'm at my wits end. I believe I can't see the forest for the trees.

Does anyone have an idea what's wrong?

[dougp]

You haven't really defined a general requirement.  That would help.  The little bit you provided seems to indicate this is a requirement for a specific report, not for the framework manager model.

From the little bit of data and description you provided, it seems you need the measure to be 0 for every date within a date range.  Assuming that's true, you'll want to define a query subject that returns all possible dates within all appropriate ranges.  Here it's OK to have extra rows.

This may be possible using the relatively new time intelligence features.  I don't use them yet, so I don't know.
Cognos may have a more elegant way to do this.  My solution here is brute force.

Once you have all possible dates for all cases in your data, join that to the rest of the data:

Code Select Expand


select <some fields>
, coalesce([Measure], 0) as Measure
, [date].[date]

from [date] d
  left outer join <the rest of your data> source on source.[date] = d.[date]


Now you should get

Code Select Expand


Date           Measure
11/01/2022     2
11/02/2022     4
11/03/2022     0
Average        2


You'll need to perform other filtering as well to limit the date range to match the source data.