Hello,
I'm working on a report in which we a testing a new calculation method for contribution margin. One of the tasks involve creating a measure to be included in both table and visual objects, that enables the user to change the period that is calculated.
Current idea is to use the Parameter Numeric Range (1-24 months) and use that as a variable in the DATEADD function. However, I can't get this to work correctly, as I'm only able to return the sum of the measure for the specific start month selected (i.e., if selecting 4, the measure should calculate a cumulative sum of the 4 latest months prior to current month).
The current DAX measure is shown below and is returning the sum of the period 4 months prior to the comparison/current period. Worth noting, the data model is a composite model with below DAX relating to the only imported table apart from the DQ data model. However, this DAX should also be copied to other sales variables subsequently, that is resided in the DQ data model - based on current research, since the Date Dimension is within the DQ model, this shouldn't be an issue in any case?
At last, below measure was formulated by a former colleague, no longer part of the company.
_Estimated Landed Cost (N-Months Ago) =
Var Max_Date = MAX(DateDim[Date])
VAR __year = YEAR(Max_DAte)
VAR __day = DAY(Max_DAte)
VAR __month = MONTH(Max_DAte)
VAR _NMonth = EOMONTH(Max_Date,-Parameter[Parameter Value]-1)+1
VAR _N_Months_Ago = DATE(__year, -MONTH(_NMonth),__day)
VAR Result = CALCULATE([_Estimated Landed Costs], DateDim[Date]=_NMonth)
Return
Result
Hope any can help on this!
