I’m attempting what seems simple: A customer starts an incentive program on a particular date. I need to calculate their consumption one year prior to program start and one year post program start to evaluate program effectiveness. Here’s the DAX for the Prior Year calculation:
Is the error happening because the Program Start Dates are not unique in the Program Participant table? If so, what approach can I take? The data set will have customers who enroll in multiple programs and those programs may have the same start date or different start dates. Thank you.
This revised DAX code does not throw an error, however, the result is incorrect. It is filtering from the MIN date in the table for every account, which is what I’m telling it to do, but I need the filtering to go back one year from the MIN start date of each account. Does this need to be modeled differently?
Program Consumption - Prior Year =
VAR ProgramStartDate = MIN('Program Participants'[Program Start Date])
DATESBETWEEN(Dates[Date], DATEADD('Dates'[Date], ProgramStartDate -1, YEAR),