Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Previous N week data

Hi,   I have a table that captures registrations for events and this table has 2 date fields - RegistrationDate and EventStartDate.   I need to calculate the cumulative No. of registrations for e...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

The criticality of my requirement is that we have 2 separate date fields and the calculation is based on both.

No. of registrations are being calculated on the RegistrationDate, whereas the offset needs to be calculated on the EventStartDate.