Defer Dates...divide based on date..and sum the results
I have a very specific calculation and I'm hoping to use 'calculated groups' to maybe optimize the performance. I have to defer sales date by 12 months...the 1st and last month are then divided by 24 and everything in between is divided by 12. I then 'sum' the data by the deferred months. I have this working, but I'm using an if/then statement with division to get the 24/12 part...and this is not giving me the correct total columns. I feel like I'm almost there, but can't seem to get the total correct. Below is the dax: Var NumOfMonths = 12 Var ReferenceDate = SELECTEDVALUE('DeferDatet'[Defer Date]) Var Referencemonth = MONTH(ReferenceDate) Var ReferenceSalesMonth = SELECTEDVALUE('SalesDate'[Month num]) Var SpreadDates = DATESINPERIOD( 'SalesDate'[Sales Date], ReferenceDate, NumOfMonths, MONTH ) Var Calc = CALCULATE( 'Sales Measures'[Sales Count], REMOVEFILTERS('DeferDate'), KEEPFILTERS( SpreadDates), USERELATIONSHIP('DeferDate'[Defer Date],'SalesDate'[Sales Date])) Var Result = if(Referencemonth = ReferenceAcctMonth,divide(Calc,24),DIVIDE(Calc,12)) return I can provide a sample pbix if needed.