Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 =
Any support you are able to provide will be greatly appreciated!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |