aggregate count over month

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

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

[Anonymous]

@v-ljerr-msftThanks for the solution.It worked!!!