Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

aggregate count over month

Dear Team,

I have a table with students id and date attended.I have a requirement for line chart to show count of students.The x axis will be months and y axis represent the count.I need to show 2 charts. In the first graph I must plot count of students attended in one month which I was able to show.In the second graph, requirement is to count number of students attended by 3 months i.e on jan we must show sum of counts of students attened in nov,dec and jan,for feb-dec,jan,feb etc…Is this possible to show?Pls advice as required.Thank you for your time.

 

Regards

Rishi

Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    Could you try the formula below to see if it works in your scenario? :smileyhappy:

    Measure =
VAR __LAST_DATE =
    ENDOFMONTH ( 'DimDate'[Date].[Date] )
VAR __DATE_PERIOD =
    DATESBETWEEN (
        'DimDate'[Date].[Date],
        STARTOFMONTH ( DATEADD ( __LAST_DATE, -3, MONTH ) ),
        ENDOFMONTH ( __LAST_DATE )
    )
RETURN
    CALCULATE ( COUNTROWS ( 'Table1' ), __DATE_PERIOD )

    Note:

    1. "DimDate" table is an individual Calendar table, if you don't one yet, you can use CALENDARAUTO to create it and then create a relationship between your fact table(with students id and date attended) and this DimDate table.

    DimDate = CALENDARAUTO()

    2. Replace "Table1" with your real fact table name.

     

    Regards

2 Replies

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

    Hi Anonymous,

     

    Could you try the formula below to see if it works in your scenario? :smileyhappy:

    Measure =
VAR __LAST_DATE =
    ENDOFMONTH ( 'DimDate'[Date].[Date] )
VAR __DATE_PERIOD =
    DATESBETWEEN (
        'DimDate'[Date].[Date],
        STARTOFMONTH ( DATEADD ( __LAST_DATE, -3, MONTH ) ),
        ENDOFMONTH ( __LAST_DATE )
    )
RETURN
    CALCULATE ( COUNTROWS ( 'Table1' ), __DATE_PERIOD )

    Note:

    1. "DimDate" table is an individual Calendar table, if you don't one yet, you can use CALENDARAUTO to create it and then create a relationship between your fact table(with students id and date attended) and this DimDate table.

    DimDate = CALENDARAUTO()

    2. Replace "Table1" with your real fact table name.

     

    Regards