Forum Discussion
Using End of Month as outer context to show active subscriptions per month
Hi EliasAR
In order for your duration calculation (yellow highlighted part) to perform in the manner you intended, you need to have the calendar table and your fact tables as disconnected tables. This is because you have start date and end date in your fact table (two date fields) and because of multiple date fields, you should not create relationship with the calendar table for it to calculate the duration properly.
The technique used is similar to headcount formula, albeit amounts are involved in your service revenue projection.
The link below shows an example of the use of disconnected tables to achive the similar result which you require relating to the duration calculation.
https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/
Best regards,
Super UserHi EliasAR
In order for your duration calculation (yellow highlighted part) to perform in the manner you intended, you need to have the calendar table and your fact tables as disconnected tables. This is because you have start date and end date in your fact table (two date fields) and because of multiple date fields, you should not create relationship with the calendar table for it to calculate the duration properly.
The technique used is similar to headcount formula, albeit amounts are involved in your service revenue projection.
The link below shows an example of the use of disconnected tables to achive the similar result which you require relating to the duration calculation.
https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/
Best regards,