I am working with an SQL Server data source concerning insurance policies query that is almost 500 lines long and has multiple CTE's some of which Left Join to the main query to achieve results.
This is the result for one account The number for "expiring premium including endorsements", "renewal premium including endorsements and "premiumn change including endorsements" are all correct.
The report calculations are all based on the month of Expiration date. I have a request to break down what is counted by expiration date for renewed policies by bound date.
I added bound date to the CTE that is returning renewals, but when a policy has more than one bound date, two lines return and the "Expiring premium including endorsements" gets counted twice and the "premiumn change including endorsements" calculation is fooled because the initial policy was not broken by 2 bound dates.
I need to somehow create an output that allows me to accurately count the "Expiring Premium including endorsements" only once with a DAX calculation, AND interogate teh value of the bind date to determine if the amoun in the renewal premium including endorsements should or should not be included.
I have been wracking my brain concerning how I may go about this and am at an impasse.
