Forum Discussion
Previous N week data
The simplest way to do this would be just add a calculated column to your table with this formula
RegistrationWeek = DATEDIFF(Table[EventStartDate], Table[RegistrationDate], WEEK)
That will give you # of weeks before the event (-1,-42, etc.). You can then make a matrix with Event on the rows and the new column on the Columns, and use a running total measure like this
Cumulative Registrations =
VAR __thisweek =
SELECTEDVALUE ( Table[RegistrationWeek] )
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table[RegistrationWeek] ),
Table[RegistrationWeek] <= __thisweek
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Microsoft EmployeeThe simplest way to do this would be just add a calculated column to your table with this formula
RegistrationWeek = DATEDIFF(Table[EventStartDate], Table[RegistrationDate], WEEK)
That will give you # of weeks before the event (-1,-42, etc.). You can then make a matrix with Event on the rows and the new column on the Columns, and use a running total measure like this
Cumulative Registrations =
VAR __thisweek =
SELECTEDVALUE ( Table[RegistrationWeek] )
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table[RegistrationWeek] ),
Table[RegistrationWeek] <= __thisweek
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
This is excellent Pat... Thanks a ton.
Your solution is just about right. I can modify the rest as per the requirement.