Running total of distinct values by year

[bullius]

Hello,

I have data that shows which of our organisation's groups attended which events for the past two years. I am trying to create a chart that shows a running total of distinct groups for each year, by month. It would look something like this:

The data looks something like this:

GroupID	Event	Date
1	A	01/02/2016
1	B	01/06/2016
1	C	01/10/2016
2	A	01/02/2016
2	B	01/06/2016
3	A	01/02/2016
4	B	01/06/2016
4	C	01/10/2016
5	B	01/06/2016
1	A	01/02/2017
1	B	01/06/2017
2	B	01/06/2017
3	B	01/06/2017
3	C	01/10/2017
4	B	01/06/2017
5	B	01/06/2017
6	B	01/06/2017
7	C	01/10/2017

I am able to create a running total for the whole period, but not one that resets at the beginning of each year.

Any help welcome.

Thanks!

[Anonymous · Accepted solution]

Create a Calculated Column "Year" and then Create a Measure 

Running Total =
CALCULATE (
    DISTINCTCOUNT ( Table1[GroupID] ),
    FILTER (
        ALL ( Table1 ),
        YEAR ( Table1[Date] ) = MAX ( Table1[Year] )
            && Table1[Date].[Date] <= MAX ( Table1[Date].[Date] )
    )
)

This will reset the Running Total for every Year you have.