Rolling average formula
I'm currently trying to create a formula to calculate the 3 week rolling average, the reason we're doing it in formulate rather than through the context calculations is because it will form part of a wider measure list.
I wrote this formula to try and keep the first two line of data blank, so the rolling calculation starts lagged, however it doesn't seem to be working. Is there anything i've missed?
3 replies
-
Hi
The example above is using MDX (as Im assuming it is connecting to an MS tabular/olap model with the use of the IIF) , instead of trying to see where Lag(1) = 'NULL' you can use the expression IsEmpty() . As the lagged field is empty (it doesn't exist as a dimensional combination for the metric) this will detect and give you the functionality you require.
so in my example I have this as my formula
which returns this
I hope this helps
thanks
Mark.