Forum Discussion
12-month rolling calculation with another moving date variable (Second Attempt - More Description)
Your CostR12 measure can be simplified (even though that may be giving you the correct answer). Please explain what you want to calculate via that measure. Then we will go to answering your actual question asked.
Every quarter I need to know see my 12-month rolling costs, which I've done with my COSTR12.
All of the costs in the 'Claims Costs Transactions' table are associated with claims using the [ClaimantAccountID] or [ReferenceNu] fields, and some of those claims have a sum of [NbDays]>=180 in the 'Days Lost Transactions' table.
For each of those quarters, I want to be able to visually split the total 12-month rolling cost into cost for 180+ day claims and <180 day claims. The measure needs to sum up the [NbDays] field in the 'Days Paid Transactions' for each [ReferenceNu] IF the [DatePaidorAuthorized] is before the end of the quarter being reported on. So as we move back in time in that line graph, some of those claims have no longer reached that 180 days yet, which in turn moves those costs into the <180 day claim category.