I look forward to seeing some non-trivial examples of OFFSET, in measures that are generalised to work in arbitrary contexts. I have found that it is not very well behaved but, maybe I’m not holding my mouth right?
Hi Cool Blue, Next week, I will publish a more realistic and challenging example of OFFSET: to calculate changes in prices or inventory values. The example dataset has a daily time series of the share prices of a couple of companies. This will have - the data model is a star schema pattern of several tables rather than a single table, - the main fact table has prices for several companies, - different companies trade on different dates. To solve, we will need to use the PARTITIONBY argument and a SUMMARIZE function to wrap the ALLSELECTED() for the delta argument. Mark
@@LondonBusinessAnalyticsGroup I look forward to that with interest! I suggest you do the partition on the stock dimension and the sorting on the datetime dimension and include those in a matrix visual, along with at least one column in the fact table that is different between the current and offset. For example the “current” price, to compare with the previous one. I suspect you will be surprised.
I look forward to seeing some non-trivial examples of OFFSET, in measures that are generalised to work in arbitrary contexts. I have found that it is not very well behaved but, maybe I’m not holding my mouth right?
Hi Cool Blue,
Next week, I will publish a more realistic and challenging example of OFFSET: to calculate changes in prices or inventory values. The example dataset has a daily time series of the share prices of a couple of companies.
This will have
- the data model is a star schema pattern of several tables rather than a single table,
- the main fact table has prices for several companies,
- different companies trade on different dates.
To solve, we will need to use the PARTITIONBY argument and a SUMMARIZE function to wrap the ALLSELECTED() for the delta argument.
Mark
@@LondonBusinessAnalyticsGroup I look forward to that with interest!
I suggest you do the partition on the stock dimension and the sorting on the datetime dimension and include those in a matrix visual, along with at least one column in the fact table that is different between the current and offset. For example the “current” price, to compare with the previous one. I suspect you will be surprised.