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!
