Calculation based on Date Filter Range
I am looking for a calculation of a value divided by month; however, the number of months is dynamic depending on the overall date range that the user has set. For example, if the user set a range of February to May (total of four months), then the calculation would be VAL / 4. If the user changed that range to January to June, then the calculated field would update to VAL / 6.
Parameters can allow for a number of months to be designated, but I want to sync it up with the date filter. Is there any way to do that?
7 replies
-
The easy option is to create a granular measure in the model that will be a count distinct on the months (or actually on the full month name or something like that).
this measure will always aggregate to the total number of months in the context of the selection (notice on which table you use to create it - there's a difference between the dates table and a fact table).
then you can use any type of filter and divide any measure by this "months" measure.
let me know if it helps.
Moshe
-
Hi Moshe,
This method only works if there is data for every month. However, if I have sales for April, May, not June, and July, the distinct count will only count those months that appear in the data, making the average sales / 3 months instead of sales / 4 months. It is quite common in my dataset to have whole months with zero sales, so a simple total divided by months counted does not work.
(and btw, I tried an IfNull(sales, 0) function to fill in the gaps, but that doesn't work).
Until I can solve this limitation, this question is not fully answered.
-
Build a complete and separate date table that joins to the facts (turn on bi-directional). Either count the months off this table or sum a counter column (using a "1") on the table.
There is a widget in the data flow for creating such tables if you don't have one. If you're using a direct query approach, then manufacture one with views (hard to do).