Calculate a measure even when a dimension has no value associated

[etiennesan]

Hello everyone,

Here is my problem: I want to compare my daily orders with my monthly forecast, by assuming that my orders will be split equally on my days of the month, removing weekends, according to a calendar that I'm loading (with a column shippable equal to -1 if it is a day where we can ship, 0 if not).

I tried adding a new table for a daily forecast and it works. In blue the forecast, in red the orders.

However, with this method I end up with additional keys based on the shippingDate, and I would like to avoid that.

Therefore I tried using this formula:

Rangesum(Above(Sum(If(shippable = -1, Forecast / vNumberOfDays)), 0, RowNo()))

But then it only gets calculated for the days where I have orders:

Any idea on how to change the formula so that it's calculed for every day of the month?

 Thank you very much!

[etiennesan]

I can see it has to do with the fact that my forecast and my calendar are only linked through the orders table, but there should be a way to calculate for every value, or should I create a calendar just for the forecast too?

[etiennesan · Accepted solution]

Actually could get it to work with the following formula: using TOTAL to disregard dimension, but with special conditions to tailor my needs

If(shippable = -1, Sum(TOTAL  {$<Description={*}>}  Forecast) / vNumberOfDays, 0)