Forum Discussion

cronline's avatar
cronline
Regular Visitor
9 years ago
Solved

User Registrations by Time Period

Hi,

 

I have a User Registrations table which is populated when a user registers all their personal details. Each registration is Timestamped with a Registration Date. I would like to build a chart, let's say a line chart, that shows me Months along the horizontal axis and a count of registrations in that month along the vertical access.

 

Any help in pointing me in the right direction would be appreciated.

 

Thanks

Chris

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi cronline


    I try to reproduce your scenario on my local computer and get the expected result as follows.

    First, add a calendar table to your data model as Matt posted. The first screenshot below is the register table, the second is calendar table. Create a relationship between them.

     


    Second, create a column in calendar, and a measure using the following formula.

    MONTH = MONTH(DateTable[Date])
COUNT = CALCULATE(COUNTROWS(Register),ALLEXCEPT(DateTable,DateTable[MONTH]))


    COUNTROWS function counts the number of rows in the specified table. The reason of using the function is that same name people may register at one day. So the each row displays one custom.

    Finally, create a line chart. Select Month in Axis, count measure as value level, please review the following result.

     

    Best Regards,
    Angelia

3 Replies

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi cronline


      I try to reproduce your scenario on my local computer and get the expected result as follows.

      First, add a calendar table to your data model as Matt posted. The first screenshot below is the register table, the second is calendar table. Create a relationship between them.

       


      Second, create a column in calendar, and a measure using the following formula.

      MONTH = MONTH(DateTable[Date])
COUNT = CALCULATE(COUNTROWS(Register),ALLEXCEPT(DateTable,DateTable[MONTH]))


      COUNTROWS function counts the number of rows in the specified table. The reason of using the function is that same name people may register at one day. So the each row displays one custom.

      Finally, create a line chart. Select Month in Axis, count measure as value level, please review the following result.

       

      Best Regards,
      Angelia