Continuous Count

[Anonymous]

Target Results: 

Is to have a count of memberships which will give me a total number within a selected period of time. The membership terms are for 1 year.

- The goal is to be able to drilldown from Year/Quarter/Month/Week level.

- Ex: If I look up on January 1, there is 1 membership because Member A started their membership on Jan 1. If I look up on February 1, there are 3 memberships because Member A still has a membership, Member C started their membership on Jan 3, and Member D started their membership on Feb 1.

- Please see below for reference.

- The ultimate goal is to be able to plot this on a time based bar chart which will drill down to Year/Quarter/Month/Week. If I click on any selection (ex; random week), it will tell me the number of active memberships for that specific week. 

(Daily Output)

Membership	Jan/01	Jan/02	Jan/03	Jan/04	Feb/01
A	1	1	1	1	1
C	0	0	1	1	1
D	0	0	0	0	1
Total	1	1	2	2	3

(Monthly Output)

Membership	Jan	Feb
A	1	1
C	1	1
D	0	1
Total	2	3

Current Results/Problem To Solve: 

I am unable to figure out how to get this to work correctly. 

(Incorrect Daily Output)

Membership	Jan/01	Jan/02	Jan/03	Jan/04	Feb/01
A	1	0	0	0	0
C	0	0	1	0	0
D	0	0	0	0	1
Total	1	0	1	0	1

(Incorrect Monthly Output)

Membership	Jan	Feb
A	1	0
C	1	0
D	0	1
Total	2	1

Current Incorrect DAX = 

ActiveMembershipCount =

CALCULATE( COUNT( Contracts[members] ), FILTER( Contracts, Contracts[fr_datetime] <= LASTDATE( 'Calendar'[Date] )

|| Contracts[to_datetime] >= FIRSTDATE( 'Calendar'[Date] ) ) )

Any support you are able to provide will be greatly appreciated!

[v-shex-msft]

HI @Anonymous ,

I'm not so sure for your data structure, please share a pbix file with some sample data so that we can test on it.

In addition, you can also try to use following measure if it works:

ActiveMembershipCount =
VAR _date =
    ALLSELECTED ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNT ( Contracts[members] ),
        FILTER (
            ALLSELECTED ( Contracts ),
            Contracts[fr_datetime] <= MAXX ( _date, [Date] )
                && Contracts[to_datetime] >= MINX ( _date, [Date] )
        ),
        VALUES ( Contracts[members] )
    )

Regards,

XIaoxin Sheng